SQL Server Syntax for update via a subquery

Question

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 [http://stackoverflow.com/questions/1102673/sql-server-syntax-question][1] but I can't seem to get the alias trick to work.

I am on SQL Server 2000

Answer 1

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.

Answer 2

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'

Answer 3

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'