0

I need to update two tables with the same WHERE clause. The WHERE clause has hundreds of IDs so I want to avoid writing it twice like this...

UPDATE TABLE1
SET (values...)
WHERE ID in (list of IDs)

UPDATE TABLE2
SET (values...)
WHERE ID in (list of IDs)  -- this is the same statement as above

Is there a way to merge these with one WHERE statement?

GMB
  • 216,147
  • 25
  • 84
  • 135
John
  • 371
  • 2
  • 4
  • 16
  • 1
    No - one update statement will directly update only one table. Put your IDs into a temp table (or table variable) and then join to update. – SMor Nov 02 '20 at 17:29
  • Does this answer your question? [How to update two tables in one statement in SQL Server 2005?](https://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005) – Adrian J. Moreno Nov 02 '20 at 17:31
  • Per the linked SO answer, make sure to wrap your update statements in a `transaction` in order roll back the `update` if part of it fails for any reason. – Adrian J. Moreno Nov 02 '20 at 17:34

2 Answers2

0

SQL Server does not support multi-table updates.

You can, however, put these ids in a table, that you can then use in your queries.

create table id_table (id int);  -- or whathever datatype you need
insert into id_table (id) values (1), (2), ...; -- list of ids

Then:

update t
set ...
from table1 t
where exists (select 1 from id_table i where i.id = t.id)
GMB
  • 216,147
  • 25
  • 84
  • 135
0

You can't really do this in SQL Server. Your best bet is to create a temporary table:

select *
into toupdate
from values ( . . . ) v(id)

Then you can use this in the delete statements:

update table1
    set . . .
    where id in (select id from toupdate);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786