CODEWITHSUNDEEP
sqlsql-serversql-updatecaseexists
ang
ang

Reputation: 1581

How to Update if a Row Exists on Another Table (SQL)

I am trying to update a column on a itemTable if a row with a matching iid exists on a correlated table.

itemMeta is a NoSQL style table, with duplicate iids per item on the table, and itemTable is a relational table.

update itemTable it
set hasAttributes = (select
  case when select count(1) from itemMeta where (im.iid = it.iid) 
  then 'Y' 
  else 'N' end)
from itemMeta im
where (im.iid = it.iid)

How do I set the column to Y on itemTable if a row with a matching iid exists on itemMeta?

Upvotes: 0

Views: 3557

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This would work in most databases, including SQL Server:

update itemTable
    set hasAttributes = (case when exists (select 1
                                           from itemMeta im
                                           where im.iid = itemTable.iid
                                          ) 
                              then 'Y' else 'N'
                         end);

If you just want to update the value to 'Y' if the value exists (and leave the existing value if present), then I would recommend:

update itemTable
    set hasAttributes = 'Y'
    where exists (select 1
                  from itemMeta im
                  where im.iid = itemTable.iid
                 );

This limits the rows being updated, so it should have better performance.

And, if you care about performance, you want an index on itemMeta(iid).

Upvotes: 6

Related Questions