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.
If every field needs to be updated to the same value, you can do that using a simple
UPDATE table SET col = new_value;
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;
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.
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.