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:

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.

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.

Learn Web Development with Go!

Sign up for my mailing list and I'll send you a FREE sample from my course - Web Development with Go. The sample includes three chapters from the book, and over 2.5 hours of screencasts.

You will also receive notifications when I release new articles, along with other freebies that I only share with my mailing list.

Avatar of Jon Calhoun
Written by
Jon Calhoun

Jon Calhoun is a full stack web developer who also teaches about Go, web development, algorithms, and anything programming related. He also consults for other companies who have development needs. (If you need some development work done, get in touch!)

Jon is a co-founder of EasyPost, a shipping API that many fortune 500 companies use to power their shipping infrastructure, and prior to founding EasyPost he worked at google as a software engineer.

More in this series

This post is part of the series, Using PostgreSQL with Go.

Spread the word

Did you find this page helpful? Let others know about it!

Vote on Hacker News

Sharing helps me continue to create both free and premium Go resources.

Want to discuss the article?

See something that is wrong, think this article could be improved, or just want to say thanks? I'd love to hear what you have to say!

You can reach me via email or via twitter.

Recent Articles All Articles Mini-Series Tags About Me Go Courses

©2018 Jonathan Calhoun. All rights reserved.