The UPDATE Statement

Part of the You Can Learn SQL series.
By Ken Brown
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'
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
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.

Home| About Us | NewsLetters | Contact Us |

Copyright © 2004-2014 You Can Learn Series

You Can Learn Series Home Page You Can Learn Series
You Can Learn Series Home Page Home            About the creators of YouCanLearnSeriesAbout Us      Contact YouCanLearnSeriesContact Us      Site map of YouCanLearnSeriesSite Map      Privacy Policy of You Can Learn SeriesPrivacy Policy       Terms of Use of You Can Learn Series web siteTerms of Use       YouCanLearnSeries RSS feed for SQL tips and tutorials     
C# and ASP.Net Programming TipsC# and ASP.Net      SQL Tutorial, tips and tricks for DBA'sSQL       Landscaping Tips for Home GardenersLandscaping       WeightLifting Tips for the Young and OldWeightLifting       Good health TipsGoodHealth       Learn about Great Travel DestinationsTravel     Web Business TipsWeb Business     

You Can Learn C# and ASP.Net SeriesC# and ASP.Net

You Can Learn SQL SeriesSQL
  Data Definition LanguageData Definition Language
  Data Manipulation LanguageData Manipulation Language
  What is a DatabaseWhat is a Database
  What is SQLWhat is SQL
  SELECT StatementSELECT Statement
  INSERT StatementINSERT Statement
  DELETE StatementDELETE Statement
  UPDATE StatementUPDATE Statement

You Can Learn Landscaping and Gardening SeriesLandscape Tips
You Can Learn WeightLifting SeriesWeightLift Tips
You Can Learn Good Health SeriesGood Health Tips
You Can Learn Series NewslettersNewsletters
Learn about Great Travel DestinationsTravel
You Can Learn Series Web Business TipsWeb Business Tips

Bill Welter - The Prepared Mind of a Leader

Americas Public Schools