Some data may never change. Yet, other data will change. In SQL, you modify existing rows of data with the UPDATE command. UPDATE is a powerful command as it can potentially change multiple rows of data in a single execution – for better or worse. UPDATE is categorized as a DML command which means: Data Manipulation Language. Let’s learn how to use this integral command with examples…
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.18
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
SQL CRUD Basics Series
Be sure and visit the accompanying blog posts in the series so far:
Recall from SQL CRUD Basics Part 1 – Create, we populated a ‘friends’ table with rows of data using the INSERT command. For a refresher, let’s revisit the present data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> SELECT * -> FROM friends; +------------+-----------+--------------+------------+------+ | first_name | last_name | phone_num | birthday | age | +------------+-----------+--------------+------------+------+ | Jim | Dandy | 476-111-1122 | 1977-07-19 | 42 | | Tara | Runner | 777-767-9900 | 1980-01-23 | 39 | | Max | Maxer | NULL | 1975-01-23 | 44 | | Mary | Moore | NULL | 1978-09-23 | 41 | | Charlie | Charles | 888-767-2323 | 1971-08-22 | 48 | | Humpty | Dumpty | 118-257-7344 | 1971-11-22 | 48 | | Roger | Dodger | 234-767-3983 | 1975-08-22 | 44 | +------------+-----------+--------------+------------+------+ 7 rows in set (0.00 sec) |
Let’s jump right in with UPDATE.
Basic UPDATE syntax follows this pattern:
1 2 3 | UPDATE some_table_name SET some_column_value = desired_value -- multiple columns are optional also WHERE some_conditonal; -- optional |
Note: MySQL’s (along with other SQL dialects) UPDATE statement has many options that I won’t necessarily cover in this post. Visit the official documentation to learn more about them if you are interested.
If you notice from the query results above, friend Max Maxer has the NULL marker as the ‘phone_num’ column value. Suppose Max now has a phone number and you want to store it in the table with the rest of his information.
This is the case in which you use UPDATE; to change existing rows of data.
Let’s go ahead and do that now with this UPDATE statement:
1 2 3 4 | mysql> UPDATE friends -> SET phone_num = '398-392-5656'; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 |
Uh-oh…
Rows matched: 7
Changed: 7
Did you notice the returned meta-data tags? My task was to just add in Max’s new phone number. But, what did I actually just do? And more importantly, what data is in the table now?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> SELECT * -> FROM friends; +------------+-----------+--------------+------------+------+ | first_name | last_name | phone_num | birthday | age | +------------+-----------+--------------+------------+------+ | Jim | Dandy | 398-392-5656 | 1977-07-19 | 42 | | Tara | Runner | 398-392-5656 | 1980-01-23 | 39 | | Max | Maxer | 398-392-5656 | 1975-01-23 | 44 | | Mary | Moore | 398-392-5656 | 1978-09-23 | 41 | | Charlie | Charles | 398-392-5656 | 1971-08-22 | 48 | | Humpty | Dumpty | 398-392-5656 | 1971-11-22 | 48 | | Roger | Dodger | 398-392-5656 | 1975-08-22 | 44 | +------------+-----------+--------------+------------+------+ 7 rows in set (0.00 sec) |
Oops…
Herein lies a super-important concept. Modifying existing data should be exercised with extreme caution.
You can see that all rows ‘phone_num’ column values are now that of Max’s new number, 398-392-5656.
Why did that happen in this query? Because no WHERE clause was used to filter and target the correct row.
Not at all what was intended.
Recommended Reading
Truth be told, the above UPDATE command was executed in a TRANSACTION, allowing me to basically undo those unwanted changes to the ‘friends’ table from the previous UPDATE command. TRANSACTION‘s are beyond the scope of this post, but they are highly important in addition to SAVEPOINT‘s and isolation levels in general.
Feel free to visit these posts if you’re interested in basic examples and information on TRANSACTION‘s and SAVEPOINT‘s:
The key to targeting a specific row or set of rows for UPDATE, is using a predicate filter in the WHERE clause.
I always try and practice the adage of, if you can’t SELECT it, then you are not going to UPDATE the correct row(s) either.
Having learned about WHERE clause predicate tests in the post, SQL CRUD Basics Part 2 – Read, those same principles apply and can be used in UPDATE statements.
All that said, I learn best by doing so here is an example exploratory query used to determine the correct row to apply the desired changes to:
1 2 3 4 5 6 7 8 9 10 11 | mysql> SELECT * -> FROM friends -> WHERE first_name = 'Max' -> AND -> last_name = 'Maxer'; +------------+-----------+-----------+------------+------+ | first_name | last_name | phone_num | birthday | age | +------------+-----------+-----------+------------+------+ | Max | Maxer | NULL | 1975-01-23 | 44 | +------------+-----------+-----------+------------+------+ 1 row in set (0.00 sec) |
Then, to UPDATE the correct row with Max’s new phone number, you just remove the SELECT syntax and replace it with the UPDATE-specific syntax. The WHERE clause predicate tests can stay. We can see we have successfully queried the correct row to through the SELECT query.
1 2 3 4 5 6 7 | mysql> UPDATE friends -> SET phone_num = '398-392-5656' -> WHERE first_name = 'Max' -> AND -> last_name = 'Maxer'; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
I can then execute that same exploratory SELECT statement used to isolate Max’s specific row, and see the updated ‘phone_num’ column value just modified:
1 2 3 4 5 6 7 8 9 10 11 | mysql> SELECT * -> FROM friends -> WHERE first_name = 'Max' -> AND -> last_name = 'Maxer'; +------------+-----------+--------------+------------+------+ | first_name | last_name | phone_num | birthday | age | +------------+-----------+--------------+------------+------+ | Max | Maxer | 398-392-5656 | 1975-01-23 | 44 | +------------+-----------+--------------+------------+------+ 1 row in set (0.00 sec) |
Multiple columns in one go…
To UPDATE multiple columns in one statement, simply separate them with a comma. However, there is only one call to SET just the same.
In the example UPDATE statement below, I change friend Marry Moore’s ‘phone_num’ and ‘birthday’ column values:
1 2 3 4 5 6 7 8 | mysql> UPDATE friends -> SET phone_num = '212-543-9420', -> birthday = '1978-03-23' -> WHERE first_name = 'Mary' -> AND -> last_name = 'Moore'; Query OK, 1 row affected (0.25 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
And the results:
1 2 3 4 5 6 7 8 9 10 11 | mysql> SELECT * -> FROM friends -> WHERE first_name = 'Mary' -> AND -> last_name = 'Moore'; +------------+-----------+--------------+------------+------+ | first_name | last_name | phone_num | birthday | age | +------------+-----------+--------------+------------+------+ | Mary | Moore | 212-543-9420 | 1978-03-23 | 41 | +------------+-----------+--------------+------------+------+ 1 row in set (0.00 sec) |
In storing any data, chances are that data will have to be updated from time to time. Using a sound WHERE clause in UPDATE statements mitigate the risk of targeting – and modifying – incorrect rows. Yet, if you need to perform a mass UPDATE among many rows, that is absolutely possible as well (Remember in the opening example query, I did just that). Just filter accordingly – or remove all-together – the WHERE clause and you’re off to the races.
You now know how to change any present data in your database tables. SQL CRUD mastery is just within your reach. But, there is just one more command we have to cover and round out the CRUD acronym: DELETE, and that is coming in the next post. Be sure and subscribe via the button in the sidebar so you don’t miss it!
Thanks for reading!!!
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.