CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
MAW74656
MAW74656

Reputation: 3539

SQL server syntax error in update statement, but I can't see it

I'm getting a syntax error on this query, but I can't figure it out.

Incorrect syntax near the keyword 'group'.

I believe its on the last group by, but I don't see whats wrong. Can anyone suggest how to correct this?

UPDATE [NCLGS].[dbo].[CP_CustomerShipTo]
SET     TimesUsed = TimesUsed + B.NewCount
from [NCLGS].[dbo].[CP_CustomerShipTo] CST
INNER JOIN (    
    Select
        PKH.CompanyCode, 
        PKH.CompanyName, 
        PKH.Addr1, 
        PKH.Addr2, 
        PKH.City, 
        PKH.State, 
        PKH.Zip, 
        Count(recid) As NewCount
    from avanti_packingslipheader PKH 
    where pksdate > dbo.ufn_StartOfDay(DATEADD(d, -1, GETDATE() ) )   
    group by 
        PKH.CompanyCode, 
        PKH.CompanyName, 
        PKH.Addr1, 
        PKH.Addr2, 
        PKH.City, 
        PKH.State, 
        PKH.Zip 
) B 
ON CST.CustomerCode     =   B.CompanyCode
   AND CST.ShipToName       =   B.CompanyName
   AND CST.ShipToAddress1   =   B.Addr1
   AND CST.City             =   B.City
   AND CST.PostalCode       =   B.Zip

group by 
    PKH.CompanyCode, 
    PKH.CompanyName, 
    PKH.Addr1, 
    PKH.Addr2, 
    PKH.City, 
    PKH.State, 
    PKH.Zip

BACKGROUND - I'm trying to do an update statement with a Count(), but of course you can't use agg. functions in an update set statement, so I'm trying to use a subquery.

Upvotes: 0

Views: 447

Answers (3)

Abe Miessler
Abe Miessler

Reputation: 85056

Try removing the last Group By. What exactly are you hoping this last group by will do?

Upvotes: 2

Bohemian
Bohemian

Reputation: 425083

Change the code to this:

update mytable set
mycolumn = mycolumn + (select x from ...);

Upvotes: 0

Andriy M
Andriy M

Reputation: 77687

You have already got GROUP BY inside the subselect, so what does the outer GROUP BY stand for?

You can't reference an alias in a subselect from an outer GROUP BY. But in any event you can't use GROUP BY with an UPDATE statement, and that's what the error message is about.

Upvotes: 3

Related Questions