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.
You must have a
This article assumes you have the
users table that was created in one of the earlier tutorials. You can follow along with the past tutorial to do this, or you can quickly create a table with the following SQL:
CREATE TABLE users ( id SERIAL PRIMARY KEY, age INT, first_name TEXT, last_name TEXT, email TEXT UNIQUE NOT NULL );
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 -
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
INTO to declare our table, instead we will just type the table after the
UPDATE users ...
Don’t ask me why SQL decided to make all three of these commands different. I really don’t know and I haven’t ever dug into it. Let’s just all hope there was a good reason for it move along.
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 = 'email@example.com';
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
firstname.lastname@example.org you could do so by running the following SQL.
UPDATE users SET first_name = 'Johnny', last_name = 'Appleseed' WHERE email = 'email@example.com';
That’s it! Every record that matches the
WHERE query will be updated, and all other records will be ignored.
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;
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;
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.
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.
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!
Sharing helps me continue to create both free and premium Go resources.
Want to discuss the article?
©2018 Jonathan Calhoun. All rights reserved.