Update Data - UPDATE

Insert a New User

Now let's see how to update data using DB Browser.

Let's add a new row to the users table and then modify the data in that row.

INSERT INTO users VALUES(5,'new@gmail.com','New','Person','nperson');

Here is the users table with the inserted row:

Update with SQL

Let's quickly check how to update data with SQL:

UPDATE users
SET email = "nobody@gmail.com"
WHERE last = "Person";

This means, more or less:

Hey SQL database 👋, I want to UPDATE the table called users.

Please SET the value of the email column to nobody@gmail.com...

...for each of the rows WHERE the value of the column last is equal to "Person".

In a similar way to SELECT statements, the first part defines the columns to work with: what are the columns that have to be updated and to which value. The rest of the columns stay as they were.

And the second part, with the WHERE, defines to which rows it should apply that update.

In this case, as we only have one user with the last name "Person", it will only apply the update in that row.

Info

Notice that in the UPDATE the single equals sign (=) means assignment, setting a column to some value.

And in the WHERE the same single equals sign (=) is used for comparison between two values, to find rows that match.

This is in contrast to Python and most programming languages, where a single equals sign (=) is used for assignment, and two equal signs (==) are used for comparisons.

You can try that in DB Browser for SQLite:

After that update, the data in the table will look like this, with the new email for "Person":

id	email	first	last	username
1	colrc@gmail.com	Lawrence	Nicodemus	original
2	jdoe@gmail.com	John	Doe	jdoe
3	jadoe@gmail.com	Jane	Doe	jadoe
null	rdoe@gmail.com	Robert	Doe	rdoe
5	nobody@gmail.com ✨	New	Person	nperson

Tip

It will probably be more common to find the row to update by id, for example:

UPDATE users
SET email="nobody@gmail.com"
WHERE id = 5;

But in the example above I used last to make it more intuitive.

Recap

Changing fields is easy. Use an UPDATE statement and list the fields you want and their new values, seperated by commas.

Later on, we'll see what happens when INSERT, DELETE, and UPDATE run up against constraints. More about that when we add constraints in the JOIN section.