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:

id(PK) name age state email
1 Paul 24 Michigan paul@example.com
2 Molly NULL New Jersey NULL
3 Robert 19 New York NULL

There are a few incomplete records in this table. When the users give us the missing info, we can add it using UPDATE statements.

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 id.

So let's update the email address using this query:

UPDATE users
SET email="robert@example.com"
WHERE id=3;

Now the table will look like this:

id(PK) name age state email
1 Paul 24 Michigan paul@example.com
2 Molly NULL New Jersey NULL
3 Robert 19 New York robert@example.com

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="molly@example.com"
WHERE id=2;

The table will now look like this:

id(PK) name age state email
1 Paul 24 Michigan paul@example.com
2 Molly 22 New Jersey molly@example.com
3 Robert 19 New York robert@example.com

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 WHERE clause.

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.

id(PK) name age state email
2 Molly NULL New Jersey NULL

Conclusion

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 UPDATE statement.

Thanks for reading!