Table Querying   «Prev  Next»

Lesson 12 UPDATE with the WHERE clause
Objective Create an UPDATE statement that will change the city and state of one of the rows in the table.


Remember when we talked about having a unique identifier for each row in every table?
You start to see why it is so important to have a unique identifier, or key on each row. That is the column you want to use in your WHERE clause. To make the earlier statement use this value, change the WHERE clause as follows:

UPDATE customer SET city='Seattle' 
WHERE customerID=3

Question: What is the difference?
Answer: What if you had more than one customer with the last name you indicated? You would end up updating both rows, when you really wanted to update only one. By using the the customer ID (or whatever other unique identifier you have set up), you will know that you will be updating one and only one row in the database.
It is extremely important that you include the WHERE clause in each and every UPDATE statement that you issue. Make sure that you experiment with the WHERE clause. It will be key to your use of SQL and will affect the UPDATE, DELETE, and SELECT statements for nearly every application you can imagine.

SQL Queries

Update Statement - Exercise

Try this exercise to practice using unique identifiers with the UPDATE statement.
Update Statement - Exercise


SQL supports rows, not tuples; in particular, it supports row types, a row type constructor, and row value constructors. Rows, unlike tuples, have a left to right ordering to their components. For example, the expressions ROW(1,2) and ROW(2,1), both of which are legitimate row value constructor invocations in SQL, represent two different SQL rows.
Note: The keyword ROW in an SQL row value constructor invocation is optional; in practice, it is almost always omitted. Thanks to that left to right ordering, row components ("fields") in SQL can be identified by ordinal position instead of by name. For example, consider the following row value constructor invocation (actually it is a row literal, though SQL does not use that term):
( 'S1' , 'Smith' , 20 , 'London' )

This row clearly has (among other things) a component with the value 'Smith'; logically speaking, however, we cannot say that component is "the SNAME component", we can only say it is the second component. I should add that rows in SQL always contain at least one component; SQL has no analog of the 0-tuple of the relational model (there is no "0-row").
Recall the example involving the SQL row variable SRV-SQL also supports a row assignment operation. In particular, such assignments are involved (in effect) in SQL UPDATE statements. For example, the following UPDATE statement
SET STATUS = 20 , CITY = 'London'
WHERE CITY = 'Paris';

is defined to be logically equivalent to this one (note the row assignment in the second line):
SET (STATUS , CITY ) = ( 20 , 'London' )
WHERE CITY = 'Paris';