I've got a list of buildings to update, by pulling another field from another table I've got a list of their pk/fk identifiers. I'd love to do it all at once instead of throwing dozens of individual update statements, each with one id in it.
Seems like such a simple task I'm positive it has to be easy and I'm just not seeing how to do it right yet. SQL seems to abhor using 'where x in ()" on an update.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here's what I'm trying to run.
update accountinginfo
set companyname = (select facilityname from facility)
where facilityid in (12345,12346,12347)
What's the right way to 'loop', for lack of a better term, through records like this, without using a cursor?
CodePudding user response:
Your subquery needs to tie in to the outer query.
update a
set companyname = (select facilityname f from facility WHERE f.facilityid = a.facilityid)
FROM accountinginfo a
where a.facilityid in (12345,12346,12347)
CodePudding user response:
You can also use a joined update:
update a
set companyname = f.facilityname
from accountinginfo a
join facility f on f.facilityid = a.facilityid
where f.facilityid in (12345, 12346, 12347);