{"id":20181,"date":"2020-07-24T21:59:48","date_gmt":"2020-07-25T04:59:48","guid":{"rendered":"https:\/\/careerkarma.com\/blog\/?p=20181"},"modified":"2023-12-01T03:56:05","modified_gmt":"2023-12-01T11:56:05","slug":"sql-update","status":"publish","type":"post","link":"https:\/\/careerkarma.com\/blog\/sql-update\/","title":{"rendered":"SQL UPDATE: A Guide"},"content":{"rendered":"\n

How to Use the SQL UPDATE Statement<\/h2>\n\n\n\n

One of the core features of databases<\/a> is that stored values can be updated. You can change the values of any record in a database at any time. Values inside an SQL database are changed using the SQL UPDATE statement.
<\/p>\n\n\n\n

In this guide, we\u2019re going to discuss what the SQL UPDATE statement is and how you can use it. We\u2019ll walk through examples of this statement in action to help you understand how it works.<\/p>\n\n\n\n

What is SQL UPDATE?<\/h2>\n\n\n\n

The UPDATE statement updates existing data in an SQL table.
<\/p>\n\n\n\n

It can be used to modify all records in a database or records that meet a certain condition or set of conditions. Here is the structure of a typical SQL UPDATE query:
<\/p>\n\n\n\n

UPDATE table\nSET column1 = value1, column2 = value2\u2026\nWHERE condition(s);<\/pre><\/div>\n\n\n\n
\u201ctable\u201d refers to the name of the table whose records you want to update.
<\/p>\n\n\n\n
The statements after the SET keyword are the names of the columns you want to update, followed by an equal sign, and then the value you want to assign to that column. To update multiple values, you need to separate each statement after SET with a comma.
<\/p>\n\n\n\n
Let\u2019s walk through an example of the SQL UPDATE statement. We have a table called employees which has the following values:
<\/p>\n\n\n\n
id<\/strong><\/td>name<\/strong><\/td>title<\/strong><\/td>hired_date<\/strong><\/td>salary<\/strong><\/td><\/tr>
1<\/td>Thomas Carlton<\/td>Sales Associate<\/td>09-08-2019<\/td>28000<\/td><\/tr>
2<\/td>Lisa Ingles<\/td>Sales Associate<\/td>12-09-2019<\/td>28000<\/td><\/tr>
3<\/td>Victoria Carlisle<\/td>Sales Director<\/td>04-02-2017<\/td>36000<\/td><\/tr><\/tbody><\/table>\n\n\n\n
<\/p>\n\n\n\n
We created this database in our tutorial on SQL INSERT. We\u2019re going to update a few records inside this database.<\/p>\n\n\n\n
SQL UPDATE in Action<\/h2>\n\n\n\n
Lisa Ingles has just married. Her surname has changed to Lisa Nelson. We can use an UPDATE statement to change her name in the employees table:
<\/p>\n\n\n\n
UPDATE employees\nSET name = 'Lisa Nelson'\nWHERE id = 2;<\/pre><\/div>\n\n\n\n
This command changes a single column using the SET clause: name. Let\u2019s select her record from the database to verify that the changes have been made successfully:
<\/p>\n\n\n\n
id<\/strong><\/td>name<\/strong><\/td>title<\/strong><\/td>hired_date<\/strong><\/td>salary<\/strong><\/td><\/tr>
2<\/td>Lisa Nelson<\/td>Sales Associate<\/td>12-09-2019<\/td>28000<\/td><\/tr><\/tbody><\/table>\n\n\n\n
<\/p>\n\n\n\n
Lisa\u2019s surname has been changed.<\/p>\n\n\n\n
Update More Than One Record<\/h2>\n\n\n\n
You can update multiple existing records in a table using a single UPDATE statement. Usually, a WHERE statement<\/a> is used when updating multiple columns to ensure that only certain records are updated.
<\/p>\n\n\n\n
The executive team has decided that all Sales Associates are going to be renamed to Sales Representatives. To make this change, we\u2019ll need to write an UPDATE command:
<\/p>\n\n\n\n
UPDATE employees\nSET title = 'Sales Representative'\nWHERE title = 'Sales Associate';<\/pre><\/div>\n\n\n\n
This command changes the value of \u201ctitle\u201d to \u201cSales Representative\u201d for all Sales Associates. Let\u2019s check to see if our change has been made:
<\/p>\n\n\n\n
id<\/strong><\/td>name<\/strong><\/td>title<\/strong><\/td>hired_date<\/strong><\/td>salary<\/strong><\/td><\/tr>
1<\/td>Thomas Carlton<\/td>Sales Representative<\/td>09-08-2019<\/td>28000<\/td><\/tr>
2<\/td>Lisa Nelson<\/td>Sales Representative<\/td>12-09-2019<\/td>28000<\/td><\/tr>
3<\/td>Victoria Carlisle<\/td>Sales Director<\/td>04-02-2017<\/td>36000<\/td><\/tr><\/tbody><\/table>\n\n\n\n
<\/p>\n\n\n\n
Thomas Carlton and Lisa Nelson\u2019s titles have been changed. Victoria\u2019s title remains the same because she is a Sales Director.<\/p>\n\n\n\n
Update All Records<\/h2>\n\n\n\n
By default, the UPDATE statement will amend all records in a database. You need to specify a WHERE statement<\/a> to override this behavior.
<\/p>\n\n\n\n
Every employee at our business is receiving a 5% pay increase. To make this change, we\u2019re going to use an UPDATE statement:
<\/p>\n\n\n\n
UPDATE employees\nSET salary = salary * 1.05;<\/pre><\/div>\n\n\n\n
This command amends the value of \u201csalary\u201d in every record in the database. We have used the multiplication operator (*) to increase every employee\u2019s salary by 1.05. This is equal to a 5% salary increase. Let\u2019s check our our database:
<\/p>\n\n\n\n
id<\/strong><\/td>name<\/strong><\/td>title<\/strong><\/td>hired_date<\/strong><\/td>salary<\/strong><\/td><\/tr>
1<\/td>Thomas Carlton<\/td>Sales Representative<\/td>09-08-2019<\/td>29400<\/td><\/tr>
2<\/td>Lisa Nelson<\/td>Sales Representative<\/td>12-09-2019<\/td>29400<\/td><\/tr>
3<\/td>Victoria Carlisle<\/td>Sales Director<\/td>04-02-2017<\/td>37800<\/td><\/tr><\/tbody><\/table>\n\n\n\n
<\/p>\n\n\n\n
The salaries of all our employees have been successfully amended.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
The UPDATE statement amends one or more records in a database. It is usually used with a WHERE statement to select exactly what records should be amended.
<\/p>\n\n\n\n
If you\u2019re looking for a challenge, write commands which:
<\/p>\n\n\n\n