CODEWITHSUNDEEP
sqlsql-server
kev670
kev670

Reputation: 880

compare 2 columns from 2 different tables and update a column

I want to compare a column in table a and table b

If the a value from table a can be found in table b then I want to update another column in table a with a 'yes' and if it cannot be found I want to say 'no'. This is what I have so far:

UPDATE a 
set
[CCA Match Org] = CASE WHEN b.[serial] = a.[CSI] THEN 'yes' ELSE 'no' END

My error at the moment says:

The column prefix 'b' does not match with a table name or alias name used in the query.

Upvotes: 2

Views: 2399

Answers (2)

Ignacio Quintero
Ignacio Quintero

Reputation: 143

This is a quick example, not sure if it will work in your case because you didnt give us more info on the table structures.

UPDATE a SET col='Yes' WHERE a.id IN (SELECT a.id FROM a JOIN b ON a.CSI = b.serial)

Upvotes: 0

anon
anon

Reputation:

Assuming the join is on b.[serial] = a.[CSI]:

UPDATE a
SET [CCA Match Org] = CASE WHEN b.[serial] IS NOT NULL 
  THEN 'yes' ELSE 'no' END
FROM a LEFT OUTER JOIN b
ON b.[serial] = a.[CSI];

Upvotes: 6

Related Questions