CODEWITHSUNDEEP
sqlsql-servert-sql
Tasos
Tasos

Reputation: 7577

Run UPDATE query for every row in another SELECT

I have an SQL Query that can return from 0 to let's say 20 results. Here is for example:

SELECT value_id FROM table1 t1
INNER JOIN table2 t2 ON ....
INNER JOIN table3 t3 ON ....
WHERE ....

Then, I want to run for each value_id an UPDATE query. Let's say:

UPDATE table4

SET new_value = 1

WHERE value_id IN (SELECT value_id FROM table1 t1
INNER JOIN table2 t2 ON ....
INNER JOIN table3 t3 ON ....
WHERE ....)

Can a subquery work on this? Is it performance efficient or there is another way?

Upvotes: 0

Views: 406

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your query is fine. The performance depends on how your database is structured. For instance, if the SELECT runs fast, then the UPDATE should be pretty fast (not as fast: there is more overhead for the UPDATE).

So, the answer to your question is: Yes, a subquery can work like this. Test the SELECT version (with table4) to get an idea of the affect on performance.

Upvotes: 1

Related Questions