Updating and Deleting records stored in a PostgreSQL table using SQL

If you have been following along with the posts in this series, at this point you should be able to do the following with raw SQL:

• Creating PostgreSQL databases and tables with raw SQL
• Inserting records into a PostgreSQL database using SQL
• Querying for records stored in a PostgreSQL table using SQL

If anything there looks a little fuzzy, you should click the link above and go make sure you are comfortable with the SQL involved. This is especially true if you don’t quite understand querying records, as we will need to query for specific records to tell SQL which records to update or delete. Don’t worry, I’ll wait here…

Alright, enough of the mailing list pitch. It is time to dig into updating and deleting records using raw SQL.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  age INT,
  first_name TEXT,
  last_name TEXT,
  email TEXT UNIQUE NOT NULL
);

Updating SQL records

As I mentioned earlier, updating an SQL record is really just a combination of the querying we discussed in the last article, combined with two new keywords - UPDATE and SET.

When querying we started with SELECT to tell our database that we were going to be retrieving some data. Similarly, when we were creating records we would start with the keyword INSERT to inform our database that we would be inserting a new record into the database. The UPDATE command is exactly like these two; We use it at the start or our SQL to make sure it is crystal clear that we are about to do an update.

After that things vary a little bit, but not much. When creating records we would say INSERT INTO <table_name>, and when querying records we would say SELECT * FROM <table_name>. In both cases we have a way of telling our database what table we want to interact with, and an UPDATE is no different, but we won’t be using a keyword like FROM or INTO to declare our table, instead we will just type the table after the UPDATE keyword.

UPDATE users ...

After telling your database what table you want to work with, the next step is to tell it which columns we are going to be updating with the SET command. This is illustrated below, but hold up! Don’t try to run the code below just yet, otherwise it will overwrite EVERY record in your table. Yikes!

UPDATE users
SET first_name = 'Johnny', last_name = 'Appleseed'
...

Why is this code blindly going through our table and updating everything? Well it turns out if you don’t tell SQL which specific records to update, it matches the update to ALL of them. Chances are this isn’t what you want, but there are cases where this is useful. For example, if you added a new column to your database and wanted to give everyone a default value for that column, the query above would actually work exactly as you wanted (well, you would need to add a semicolon).

Unfortunately, we don’t want to update every record right now, so we need a way to specific which records we want to update, and this is where what we learned in the querying tutorial comes into play. Remember when we wrote code like:

...
WHERE email = 'jon@calhoun.io';

Well, it turns out we can reuse this syntax to pick the records that we want to update, and the syntax is identical to what we were using before. Sweet, right?

For example, if you wanted to update the user with the email address jon@calhoun.io you could do so by running the following SQL.

UPDATE users
SET first_name = 'Johnny', last_name = 'Appleseed'
WHERE email = 'jon@calhoun.io';

That’s it! Every record that matches the WHERE query will be updated, and all other records will be ignored.

Because the WHERE query can match multiple users, you do need to be careful that your queries only grab records that you want to update. The simplest way to ensure that you only update a single record is to use the records unique ID. This is guaranteed to be unique for each record, so we don’t need to worry about it matching multiple users, and the SQL is just as easy to write.

UPDATE users
SET first_name = 'Johnny', last_name = 'Appleseed'
WHERE id = 1;

Deleting SQL records

Delete works pretty similar to updating records, but there are a few minor adjustments we need to make. Remember when we were talking about how every query has a different way of telling the database what table we want to work with? Well the DELETE command doesn’t just take a table name right after the keyword, and instead it goes back to using the FROM keyword like the SELECT statement does.

DELETE FROM users ...

Aside from that, it works pretty similar to the UPDATE command. If you don’t provide it with a WHERE clause, it will just delete all of the records in your table (so be careful!), and adding a WHERE clause is identical to above.

DELETE FROM users
WHERE id = 1;

In summary

That should be enough to get you started on your way to becoming an SQL master. We didn’t really dig too deep into anything advanced, but with everything we have learned you should be able to browse an SQL database, make some minor updates, and when you start to jump into more advanced queries you should find little bits that make sense to you as you pick it apart and learn what it does.

From here we are going to stop focusing on raw SQL and are instead going to start diving into using SQL, specifically PostgreSQL, with Go, starting with learning to connect to a postgres database with Go's database/sql package.

You can also check out the entire series, , to see what other articles are in the series and get a feel for what material will be covered as you continue.