My First UPDATE Statement
Last week we covered how to put information into a table using an INSERT statement.
This week we will learn how to make changes to data that is already in a table using an UPDATE statement. We are also going to learn all about why the WHERE clause is so important.
UPDATE
Updating information stored in a database can be as simple as identifying which column to update and specifying the new data to write in that column:
UPDATE [dbo].[Stores]
SET [StoreName] = N'West Edmonton Mall';
GO
The problem with this particular UPDATE statement, though, is that it will update every row, when we may only want to change one record. That’s why we need to add WHERE. The purpose of a WHERE clause is to restrict the data modification using a filter or limit.
The WHERE clause
In ANSI SQL (remember, ANSI is the standards organisation that defines how SQL works with different database platforms), a WHERE clause goes somewhere near the end of a query, to filter or limit the data being affected.
The WHERE keyword can be used on all four basic data manipulation queries: SELECT, INSERT, UPDATE and DELETE. The reason we’re only learning about it today, is that it is a critical part of the UPDATE and DELETE statements.
Limiting the damage
An UPDATE statement without a WHERE condition, or clause, will update the entire table.
Consider these two examples, and imagine what is happening in the table:
-- Without a WHERE clause
UPDATE [dbo].[Stores]
SET [StoreName] = N'West Edmonton Mall';
GO
-- With a WHERE clause
UPDATE [dbo].[Stores]
SET [StoreName] = N'West Edmonton Mall'
WHERE [StoreID] = 2;
GO;
In the first example, every single row in our table will now have ‘West Edmonton Mall’ for the store name. In the second example, only the row (or rows) that match the condition in the WHERE clause will be updated.
Notice how the WHERE condition relates to a value in the [StoreID] column, which is this table’s Primary Key. When updating individual rows in a table, it’s always better to use a unique value to guarantee that we only update a single row.
We could use a WHERE condition on a different column, or combinations of columns, using AND and OR logic statements. WHERE clauses can be extremely complex.
Note: the UPDATE statement will update whatever we tell it to. Even though the original value of the column was ‘West Edmonton Mall’, the database engine will happily update that value again and again, to the same string, if we ask it to.
SET
Astute readers will recognise a new keyword in the UPDATE statement: SET.
The first part of an UPDATE specifies the table we wish to modify.
Then, the SET keyword specifies the column we want to change. We can add more than one column to our SET clause, separated by commas, if we have more than one column in the same row (or rows) that we wish to update.
For instance, let’s assume the West Edmonton Mall store has a new manager. We can modify the [ManagerName] and [ManagerEmail] columns at the same time.
UPDATE [dbo].[Stores]
SET [ManagerName] = N'Wesley Wensleydale',
[ManagerEmail] = N'wesley@example.com'
WHERE [StoreID] = 2;
GO;
This operation, where more than one thing is being changed at the same time, is called a set-based operation. In other words, a set of data is being updated at once, instead of writing an UPDATE statement for every single column.
Set-based operations can run on the entire table, or on a filtered set of data specified by the WHERE clause.
Ambiguity Verboten
Unlike the INSERT statement, where column names were implied, an UPDATE statement has to explicitly list the column (or columns) that we are updating. In our examples above, we had [StoreName], [ManagerName] and [ManagerEmail]. This is because there can be absolutely no ambiguity when modifying data.
This is also why a WHERE clause is so important. I have personally run an UPDATE or DELETE without adding a WHERE clause, and it happens a lot in this field. Make sure to add a WHERE keyword before writing the rest of the statement.
Some tools that plug into SQL Server Management Studio will detect if we have missed a WHERE clause on an UPDATE or DELETE, but we can’t always rely on plugins. For example, we might have to use a different computer one day, or write our SQL code in a text editor like Notepad, and only good habits can avoid disaster.
The Final Results
If you’re following along in SQL Server Management Studio, we can run the three-column UPDATE statement like so:
UPDATE [dbo].[Stores]
SET [StoreName] = N'West Edmonton Mall',
[ManagerName] = N'Wesley Wensleydale',
[ManagerEmail] = N'wesley@example.com'
WHERE [StoreID] = 2;
GO
Once we have executed the statement (using F5 on the keyboard, or clicking the Execute button in the toolbar), we see a message in the Messages pane:
(1 row(s) affected)
Using our recently-learned skill to SELECT the data from this table, we will see the new result set, containing the new row:
Congratulations! We have modified a row of data in our table, and a SELECT query proved that it was inserted.
Next time, we will be removing data from a table using the DELETE command. Stay tuned.
Look me up on Twitter if you want to ask any questions about this series, at @bornsql.