Once you have created a table in a database, it will rarely need to stay the same forever. You will likely need to modify the records in it.
And to help you do that, there is a useful statement, aptly named
UPDATE, that you can use to change the records as needed.
Note: If the syntax presented here doesn't work, check the documentation for the implementation of SQL you are using. Most stuff works the same across the board, but there are some differences.
SQL UPDATE Syntax
To use the
UPDATE method, you first determine which table you need to update with
UPDATE table_name. After that, you write what kind of change you want to make to the record with the
SET statement. Finally, you use a
WHERE clause to select which records to change.
It's really important to use that
WHERE clause, otherwise you are going to make the same change to the whole table.
UPDATE table_name SET change to make WHERE clause to select which records to change;
SQL UPDATE Example
We have a table named
users that looks like below:
There are a few incomplete records in this table. When the users give us the missing info, we can add it using
The user Robert is missing an email address. All rows selected by the
WHERE clause will be updated, so we need to be careful: we could select the record to update using the name column, but names are not unique – we could have multiple Roberts in our table.
The best way to select a row to update it (to make sure you are updating only the row you want to update) is to use the
PRIMARY KEY column in which values are always unique. In this case that's the column named
So let's update the email address using this query:
UPDATE users SET email="email@example.com" WHERE id=3;
Now the table will look like this:
How to Update Multiple Columns at the Same Time
Molly is missing a value in two different columns. We can use a single
UPDATE statement, separating the assignments with commas, like so:
UPDATE users SET age=22, email="firstname.lastname@example.org" WHERE id=2;
The table will now look like this:
Make sure to change only the records you want to change
This is a security concern. Our examples have only few lines, but in a real life situation it could be the database of an app or website with hundreds, thousands, or even millions of users. And you don't want to cause trouble for so many people.
So before you issue an
UPDATE query, send a
SELECT query with the same
WHERE clause. If it returns the record you want to update, go for it. Otherwise you need to change the
For example, before sending the update for the user Molly, we could have sent a
SELECT statement to check that the clause we have used,
WHERE id=2, is the correct one:
SELECT * FROM users WHERE id=2;
This query returns the record below, so you are good to go with the
UPDATE query to complete the data.
Once you create your tables and add records to them, there will always be times when you need to update a row. This article explained how to do that using the SQL
Thanks for reading!