Part of the
You Can Learn SQL series.
By Ken Brown
Editor, YouCanLearnSeries.com
Updated: January 29, 2005
At some point you are going to want to update the rows in your database. What
does that mean UPDATE? Is update a keyword, a command or a statement? What is
the proper syntax with the update command? What does it do? We'll answer your
questions in this article and provide you examples of the proper use of the
update command.
In the query analyzer help window, Microsoft has a very simple definition of
the UPDATE command, "Changes existing data in a table." How easy is that? So
any time you want to change any information in a table you use the update
keyword. Now whether you change one row or all rows will depend on how you
qualify the statement.
Here is the UPDATE statement in its simplest form. Use the Northwind database
and the authors table.:
UPDATE authors
SET au_fname = 'Kenno'
WHERE au_id = '123-12-6321'
GO
In this example we are updating the authors table. For all the rows selected in
our qualifier WHERE clause, we will set the first name column, au_fname, equal
to "Kenno". If you don't add the WHERE clause, then every single first name
column will now contain "Kenno". Be sure and put in the qualifier.
So the syntax is UPDATE table name SET column name = some new
value WHERE colum name = some value to help choose the right row
What if you want to update more than one column? The syntax is almost the same.
You still use the UPDATE, SET and WHERE keywords, you just add a little more
information to the SET keyword. Instead of one column = a value, then you add a
comma and have another column = a value.
UPDATE authors
SET au_fname = 'Kenno', zip = '60610'
WHERE au_id = '123-12-6321'
The next update statement is a little more complicated. In the Northwind
database we have the orders table and the Order details table. They are
relational tables in that they have the same key of orderid. We want to take
the sum of the Order Details table unitprice column. But only where the orderid
= 10248. This shows using a subselect and a SUM keyword to get a value for the
column you want to change.
UPDATE orders
SET Freight = (SELECT SUM
([Order Details].unitPrice ) * .15
FROM [Order Details]
WHERE [Order Details].OrderID = 10248)
WHERE orders.orderid = 10248
GO
This next statement is taken directly out of the Microsoft Query Analyzer help
pages. It shows the use of the keyword FROM to arrive at a similar result as
the statement above. Here we are qualifying the UPDATE statement with both a
FROM clause and a WHERE clause. The FROM clause sets an alias table t1 equal to
the first 10 rows from the authors table after the table has been ordered by,
sorted, the last name column au_lname. Then table t1 is joined with itself the
authors table by au_id. So only the rows in the authors table in the top ten
rows after sorting descending by last name will have their state name changed.
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id
This last one needs to be dissected by most people who see it. It isn't obvious
at first determining what is occurring. If you are having trouble with it, then
take the select statement out and run just the SELECT statement. Then it will
become more clear.
This finishes our little primer on the UPDATE statement. A very powerful
command but necessary to keep our databases current with the information to run
our businesses. So when a change is made in your program, you use the UPDATE
command to correct the tables that need to change to reflect the current data.
|