CODEWITHSUNDEEP
sqlsql-server
kev670
kev670

Reputation: 880

Nest a select statement within an update statement

This is my update statement which works perfectly. If table 1 and table 2 contain an equal serial number then table 1 is updated with a yes and if it doesnt then it is updated with a no.

UPDATE dbo.table1
SET [Match] = CASE WHEN dbo.table2.[Serial Number] IS NOT NULL
THEN 'yes' ELSE 'no' END
FROM dbo.table1 LEFT OUTER JOIN dbo.table2
    ON dbo.table2.[Serial Number] = dbo.table1.[Serial Number]
AND dbo.table1.[Date] = 'JAN11' AND 
      dbo.table1.[Install] = 'new' AND
      dbo.table2.[Date] = 'JAN11' AND 
      dbo.table2.[Install] = 'new'

The problem is currently everything in table 1 that isnt being called is also updated with a no so what i want to do is first call a select statement... something like this

select * from table1 where [Date] = 'JAN11' AND [Install] = 'new'

and nest it within the update so that the relevant columns only are updated with no or yes...

Upvotes: 1

Views: 446

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

UPDATE t1
  SET [Match] = CASE WHEN t2.[Serial Number] IS NOT NULL
  THEN 'yes' ELSE 'no' END
FROM dbo.table1 AS t1 
LEFT OUTER JOIN dbo.table2 AS t2
   ON t1.[Serial Number] = t2.[Serial Number]
  AND t1.[Date] = t2.[Date]
  AND t1.[Install] = t2.[Install]
WHERE t1.[Date] = 'JAN11' 
  AND t1.[Install] = 'new';

Upvotes: 14

Related Questions