Menu
This query gives an error whenever there are more than 1 record that exists in tbl1 and not in tbl1_temp
How can I fix this so that for every record that does not exist in tbl1_temp, update the corresponding record's status in tbl1
UPDATE tbl1 SET Status = 'Finished'
WHERE id = (SELECT id
FROM tbl1
WHERE NOT EXISTS (SELECT id FROM tbl1_temp
WHERE tbl1.id = tbl1_temp.id))
784
Update won't insert records if they don't exist, it will only update existing records in the table. Save this answer.
NOT IN will produce unexpected results if you have NULL in your dataset (see buckley's answer). Best to use NOT EXISTS as a default.
NOT EXISTS is used with a subquery in the WHERE clause to check if the result of the subquery returns TRUE or FALSE. The Boolean value is then used to narrow down the rows from the outer select statement.
UPDATE tbl1
SET Status = 'Finished'
WHERE NOT EXISTS (SELECT id FROM tbl1_temp
WHERE tbl1.id = tbl1_temp.id)
Just use NOT EXISTS directly
UPDATE tbl1
SET Status = 'Finished'
WHERE NOT EXISTS (SELECT * FROM tbl1_temp
WHERE tbl1.id = tbl1_temp.id)
The equality operator works only for a single value, not for multiple values, hence the error. So instead of equation, use IN:
... WHERE id IN (SELECT ...)
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
