How to update multiple records at once with subquery in SQL

Question

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

I guess the real question is why are you storing the same data in two places. Keep one source of truth: if you need to find the facilityname then write a query or a view joining the two tables — Charlieface
– Charlieface, Commented Feb 17, 2022 at 14:46
@Charlieface The two fields can have different values. In this case, the request was "Just make the companyname match the facilityname in the other table for these" I'm only updating ~20 out of ~30,000 records. I tried joining it but it blows up when I add the table identifier, ie update accountinginfo a -- I get 'Incorrect syntax near 'a'. Expecting SET. Doesn't matter where I put the join in the query or if I try to add the identifier further down. that just throws a 'multipart identifier could not be bound.' I'll look into a view. — ProtusMose
– ProtusMose, Commented Feb 17, 2022 at 15:04

Charlieface · Accepted Answer · 2022-02-17 15:10:09Z

3

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);

answered Feb 17, 2022 at 15:10

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dogyog · Accepted Answer · 2022-02-17 14:53:20Z

1

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)

edited Feb 17, 2022 at 14:53

answered Feb 17, 2022 at 14:27

dogyog

3404 silver badges11 bronze badges

3 Comments

ProtusMose Over a year ago

So the where clause is like an ad-hoc join? I tried that but SSMS blows up on the first line "Incorrect syntax near 'a'. Expecting SET"

dogyog Over a year ago

Sorry about that, fixed it up. EDIT: When you alias in an UPDATE, need to do that in the FROM clause. And yes, the WHERE is the join, when the sub-query is in the SELECT that is how you do it, when it is in the FROM you alias the whole thing and join 'normally'.

ProtusMose Over a year ago

That works perfectly. Thank you so much. I see where I had it busted now.

Collectives™ on Stack Overflow

How to update multiple records at once with subquery in SQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related