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.
To expand on this, you can add anything to the
WHERE clause you like as long as it’s a valid expression. So to perform an update based on the value of another column in the same table, you could execute the following:
WHERE clause can contain any valid expression, you also have the possibility to do updates where multiple columns meet the criteria
As you can see, you can expand the
WHERE clause as much as you’d like in order to filter down the rows for updating to what you need.
Now what happens if you want to update rows in one table based on the condition of another table? This question leads to a few different ways you could do this.
WHERE clause can contain any valid expression, you could use a subquery:
You can also use a subquery in the
SET portion of the statement if you want to set the column to a value in another table
Perhaps an easier way is to specify multiple tables after the
UPDATE clause. Only the
SET expression will perform updates but listing additional tables will allow the tables to be included.
Similarly to expanding the
WHERE clause, the amount of tables can be expanded to include all the tables you need if you have multiple tables that need to be joined on.