How to update a column based on a filter of another column
Posted by: Matt McCormick
In this tutorial, we’ll go over the various ways to update rows in a table using SQL progressing from more general updates to more specific methods.
Full update
If every field needs to be updated to the same value, you can do that using a simple UPDATE command.
UPDATE table
SET col = new_value;Conditional update
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.
UPDATE table
SET col = new_value
WHERE col = old_value;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:
UPDATE table
SET col = new_value
WHERE other_col = some_other_value;Since the WHERE clause can contain any valid expression, you also have the possibility to do updates where multiple columns meet the criteria
UPDATE table
SET col = new_value
WHERE col = old_value
AND other_col = some_other_value;
UPDATE table
SET col = new_value
WHERE col = old_value
OR other_col = some_other_value;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.
Since the WHERE clause can contain any valid expression, you could use a subquery:
UPDATE table
SET col = new_value
WHERE other_col IN (
SELECT other_col
FROM other_table
WHERE conditional_col = 1
);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
UPDATE table
SET col = (
SELECT other_col
FROM other_table
WHERE other_table.table_id = table.id
);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.
UPDATE table, other_table
SET table.col = other_table.other_col
WHERE table.id = other_table.table_id;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.