CODEWITHSUNDEEP
sqlsql-server
cindi
cindi

Reputation: 4791

SQL Server Syntax for update via a subquery

How do I formulate this query

update forge..dimInteg2
   set duplicates = 
      (select count(*) from (select idCover 
                                 from x90..dimCover
                                 group by idCover
                                 having count(*) > 1)) 
where dimTable = 'dimCover'

to avoid this error

Line 6: Incorrect syntax near ')'.

Similar to [SQL Server subquery syntax but I can't seem to get the alias trick to work.

I am on SQL Server 2000

Upvotes: 0

Views: 659

Answers (3)

JNappi
JNappi

Reputation: 1585

You need to name the derived table.

update forge..dimInteg2 set duplicates = (select count(dimCover.idCover) from (select idCover from x90..dimCover group by idCover having count(*) > 1) dimCover) where dimTable = 'dimCover'

Upvotes: 0

butterchicken
butterchicken

Reputation: 13883

Are you missing a bracket?

update forge..dimInteg2
set duplicates = 
  (select count(*) from (select idCover 
                             from x90..dimCover
                             group by idCover
                             having count(*) > 1) ) --HERE
where dimTable = 'dimCover'

Then the alias solution should work.

Upvotes: 3

iburlakov
iburlakov

Reputation: 4232

Try to add field dimTable to second select statement:

update forge..dimInteg2
   set duplicates = 
      (select count(*) from (select idCover --, dimTable HERE
                                 from x90..dimCover
                                 group by idCover
                                 having count(*) > 1) 
where dimTable = 'dimCover'

Upvotes: 0

Related Questions