Menu
I have a table where I want to update all rows with the ID that exists in the select result.
My pseudo-code:
UPDATE mytable as t
SET t.status = 'PM'
WHERE t.ID EXISTS IN (select ID from ...)
I have managed to do the select statement, now I want to use the result of the select statement to update a table.
557
The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.
UPDATE from SELECT: The MERGE statement The MERGE statement is used to manipulate (INSERT, UPDATE, DELETE) a target table by referencing a source table for the matched and unmatched rows. The MERGE statement can be very useful for synchronizing the table from any source table.
The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.
To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.
If you remove the exists you have a valid query from what I can tell.
UPDATE mytable
SET status = 'PM'
WHERE id IN (select ID from ...)
Works for me in MySql 5.5, not sure which database you're using.
151
One cannot use substitution in the UPDATE statement. The original query should be good when you leave out the " as t" part and both "t.".
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
