[FIXED] How to update multiple records at once with subquery in SQL

Issue

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?

sample record

Solution

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)

Answered By - dogyog

Answer Checked By - Marilyn (PHPFixing Volunteer)