SQL uses the "UPDATE" statement to alter/change data in your tables. Just like the SELECT statement, you need to specify columns and a table, but the UPDATE statement also requires the new data you want to store. This data can be dynamic or static, but as in introduction, we'll use static strings or numbers to change data in a table.
To get started, you first need the syntax for a basic UPDATE statement. The following code is an UPDATE statement template:
UPDATE <table>
SET <column> = <yourdata>
One issue with the above statement – the statement has no WHERE clause. UPDATE statements don't require a WHERE clause. However, if you don't insert a WHERE clause in your UPDATE statements, you will change every record in the database. Typically, you want to change a subset of records in your tables and not all records. The error can be catastrophic for your data if you don't have a backup. You might want to update all records in your table, but for most circumstances, you use a WHERE clause. The following UPDATE statement template has a WHERE clause:
UPDATE <table>
SET <column> = <yourdata>
WHERE <clause>
The first line of code is the UPDATE statement keyword and then the table you want to edit. You need a table in your statement, which must be spelled correctly. Any mistakes in the table name and the SQL engine will give you an error.
Next, the SET statement identifies the column and the new data you want to use. Just like the SELECT statement, string and date values should be encased in single quotes. The column must be accurate too. If you misspell a column name, SQL throws you an error.
The WHERE clause is formatted in the same way you formatted it in your SELECT statements, but we'll get into WHERE clauses later.
In almost any application, the user must be able to edit data. The user could have accidentally entered a typo or he didn't enter any information and you later ask him for it in the future. For instance, your application might ask the user to enter a first and last name. Typing values into an application leads to typos. Your application could have a form that lets the user enter new information and fix any errors. The following UPDATE statement is an example of an UPDATE statement for editing a first name:
UPDATE Customer
SET first_name = ‘Tom'
WHERE CustomerId = 123
The following tables show you a before and after view of your tables. The first table displays the data before you run the UPDATE statement, and the second table shows the data after you run the UPDATE statement:
Before
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
Tim |
Smart |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
After
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
Tom |
Smart |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
In the above example, the first line of code specifies the table. In this example, the table being edited is the Customer table. The second part of the statement is the column name you want to edit. In this example, the SQL statement updates the "first_name" column with new data. The data is a string with the value "Tom." If you attempt to store a string in a field designated as a numeric value, SQL throws you an error.
Finally, the WHERE clause limits the records edited. This statement only updates one customer with an Id of 123.
The UPDATE statement doesn't limit you to one column. You can edit multiple columns at one time. Suppose your customer wants to edit both his last and first names. The following UPDATE statement edits both columns at one time but still limits it to one customer:
UPDATE Customer
SET first_name = ‘Tom', last_name = ‘Smith'
WHERE CustomerId = 123
The following two tables show you a before and after snapshot of your data when you run the SQL UPDATE statement:
Before
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
Tom |
Smart |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
After
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
Tom |
Smith |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
When you want to edit more than one column, you just append a comma to the end of the column section of the statement and add your column with a new value.
You can also edit columns with NULL values. If you recall from Lesson 2, NULL values are used when nothing is entered by the user or no value exists. Your data administrator must allow NULL values in columns for you to use them. If NULL values aren't allowed, SQL throws you an error when you edit values to NULL. For instance, you might want to reset values and ask users to reenter information because you detected that it's incorrect. You could set table column values to NULL and alert the user. The following UPDATE statement sets the user's first name to NULL:
UPDATE Customer
SET first_name = NULL
WHERE CustomerId = 123
The following tables give you a before and after snapshot:
Before
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
Tom |
Smith |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
After
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
NULL |
Smith |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
So far, you've only edited specific customers. SQL lets you update multiple records at one time. The WHERE clause in an UPDATE statement is constructed in the same way as a SELECT statement except UPDATE edits the selected records instead of just searching for them. If multiple records are returned in the WHERE clause, you update multiple records at once. The following SQL statement updates all records where the customer has a NULL value for the first_name column:
UPDATE Customer
SET first_name = ‘No Value'
WHERE first_name IS NULL
The following tables give you a before and after snapshot:
Before
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
NULL |
Smith |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
After
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
No Value |
Smith |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
Sometimes, you need to edit records based on a SELECT statement. You can't use a JOIN statement in an UPDATE statement (JOINs are discussed later), so you need a way to query another table and base results on your update. You can use a SELECT statement in your WHERE clause. For instance, you might have a table that contains a list of customers you want to edit. This external table has the customer's Id that matches the Id in your Customer table. You could use a SELECT statement in your UPDATE statement to edit your data. The following SQL query is an example:
UPDATE Customer
SET city = ‘Miami'
WHERE CustomerId IN (SELECT Id FROM ExternalTable)
Here is an example of the ExternalTable:
|
Id |
City |
State |
|
123 |
Mimi |
FL |
|
321 |
Dallas |
TX |
Here is an example of your Customer table again:
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
No Value |
Smith |
Mimi |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
Notice that the first table does not have all the data as the second. It doesn't matter to SQL because the IDs match and the sub-SELECT query is based on the ID. CustomerId and Id both have your customer numbers, and your SQL statement uses these two values to query the tables.
In the above SQL statement, the SELECT statement runs first. All IDs in the ExternalTable table are returned. SQL then matches these IDs with the CustomerId in the Customer table. If there is a match, SQL updates the Customer's city value to "Miami."
The UPDATE statement edits your customer table and does not edit anything from the second ExternalTable. The result is that now your Customer table looks like the following:
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
No Value |
Smith |
Miami |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
You can also add a WHERE clause to the SELECT clause in your UPDATE statement. For instance, your customers might make typos when spelling Dallas. You can do a global search in the SELECT statement that finds a list of customers who accidentally misspell the city as "Dllas." The following SQL example shows you how to incorporate the WHERE clause in a sub-SELECT statement:
UPDATE Customer
SET city = ‘Dallas'
WHERE CustomerId IN (SELECT Id FROM ExternalTable where city = ‘dllas')
In the above example, all customers with an ID matching the ID from the ExternalTable that also mistyped the city as "Dllas" are updated.
Here is the ExternalTable example:
|
Id |
City |
State |
|
123 |
Mimi |
FL |
|
321 |
Dllas |
TX |
The sub-SELECT statement gets all records where the value is "Dllas," which is one record. The sub-SELECT returns the Id 321, which is used in the UPDATE's WHERE clause. The result is the following changes to your data:
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
No Value |
Smith |
Miami |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
You can use a sub-SELECT with any number of WHERE clause phrases.
The UPDATE statement lets you use numeric comparisons. The examples we've used so far have been all varchar or string values. You can also perform updates based on numeric results. For instance, suppose you want to update records based on the CustomerId value. You know all customers that have an ID less than 200 have corrupted first names. You want to reset these first name values to NULL to alert your system that customers need to reenter their first names. The following UPDATE statement performs this action:
UPDATE Customer
SET first_name = NULL
WHERE CustomerId < 200
The above statement updates any user that has an ID from 1 to 199. If you want to include customers with an ID of 200, you use the "less than or equal to" comparison characters. To include the value 200, you use the following statement:
UPDATE Customer
SET first_name = NULL
WHERE CustomerId <= 200
Here are the snapshots of the before and after tables:
Before
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
No Value |
Smith |
Miami |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
After
|
CustomerId |
First_name |
Last_name |
City |
State |
|
123 |
NULL |
Smith |
Miami |
FL |
|
321 |
Frank |
Doe |
Dallas |
TX |
As you can see, the SQL UPDATE statement lets you change data when you need to edit it in your tables. Just remember to always use the WHERE clause in your statements to avoid changing all records unless you want to globally update them.
