Post reply

An aggregate may not appear in the set list of an UPDATE statement????

  • August 30, 2005 at 2:00 am

    #165861

    Hello friends !!!!

    i got one problem i created one procedure which update one column M_Name if duplicate records are there like

    CREATE PROCEDURE UpdateDuplicate_empt

    @JOBId  VARCHAR(10)

    AS

    UPDATE empt

    SET M_Name = CASE WHEN  COUNT(@JOBId) > 1 THEN 'Y'

    ELSE 'N'

    END

    WHERE JOBId = @JOBId

    (Here M_Name is updated or not that depend on JOBId value duplicate or not like that i want output)

    But when i run this i got error like-------------

    An aggregate may not appear in the set list of an UPDATE statement.

    Can any body have solution how could I remove error

    Plz Help me out

    T.I.A

    Shashank

    Regards,

    Papillon

  • August 30, 2005 at 2:15 am

    #586065

    Hi!

    What about doing the aggregate first in a temp table?

    -- untested code!  Just to give you the idea!

    select JOBID, COUNT (JOBID) as cnt

    into #temp

    from empt

    where JOBID = @JOBID

    group by JOBID

    Then do the update:

    update empt

    set M_Name = 'Y'

    where JOBID in (select JOBID from #temp where cnt > 1)

    That's the big picture, some more work is needed (especially to set it to 'N' if there is only one - another update would do the trick, but some outer join may also work...)  Not talking about performance!

    Bye,

    Xavier

  • August 30, 2005 at 2:17 am

    #586067

    See my post over here

    http://www.sqlserver.org.au/forums/ShowPost.aspx?PostID=764

     

    --------------------
    Colt 45 - the original point and click interface

  • August 30, 2005 at 2:42 am

    #586070

    Hey..philcart

    You really makes me smile

    Thanks very much that is gr8 help from you!!!!!!

    Regards

    Shashank

     

    Regards,

    Papillon

  • August 30, 2005 at 4:00 am

    #586081

    Hello friends

    philcart has given me some code like

    UPDATE empt

    SET M_Name = CASE WHEN JobCount > 1 THEN 'Y'

    ELSE 'N'

    END

    FROM empt ,

    (

    SELECT COUNT(JObID) as JobCount FROM empt WHERE JOBId = @JOBId

    ) cnt

    WHERE JOBId = @JOBId

    but here i am facing some problem

    1) records for jobid are in lacks and another column is DAYDATE that perticular date when this JobId comes

    so i want one query only which update my records accordingly for Duplicate jobid as well as for date column DAYDATE

    (may be both jobid and DAYDATE columns have duplicate records)

     

    T.I.A

    Shashank

    Regards,

    Papillon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply