How to Update Your Existing SQL Data
Because the world is constantly changing, the SQL databases used to model aspects of the world also need to change. A customer may change her address. The quantity of a product in stock may change. A basketball player’s season performance statistics change each time he plays in another game. If your database contains such items, you have to update it periodically.
SQL provides the UPDATE statement for changing data in a table. By using a single UPDATE statement, you can change one, some, or all rows in a table. The UPDATE statement uses the following syntax:
UPDATE table_name SET column_1 = expression_1, column_2 = expression_2, ..., column_n = expression_n [WHERE predicates] ;
The WHERE clause is optional. This clause specifies the rows that you’re updating. If you don’t use a WHERE clause, all the rows in the table are updated. The SET clause specifies the new values for the columns that you’re changing.
Consider this CUSTOMER table.
Customer lists change occasionally — as people move, change their phone numbers, and so on. Suppose that Abe Abelson moves from Springfield to Kankakee. You can update his record in the table by using the following UPDATE statement:
UPDATE CUSTOMER SET City = 'Kankakee', Telephone = '666-6666' WHERE Name = 'Abe Abelson' ;
This statement causes the changes shown in Table 6-2.
You can use a similar statement to update multiple rows. Assume that Philo is experiencing explosive population growth and now requires its own area code. You can change all rows for customers who live in Philo by using a single UPDATE statement, as follows:
UPDATE CUSTOMER SET AreaCode = '(619)' WHERE City = 'Philo' ;
The table now looks like the one shown in Table 6-3.
Updating all the rows of a table is easier than updating only some of the rows. You don’t need to use a WHERE clause to restrict the statement. Imagine that the city of Rantoul has acquired major political clout and has now annexed not only Kankakee, Decatur, and Philo, but also all the cities and towns in the database. You can update all the rows by using a single statement:
UPDATE CUSTOMER SET City = 'Rantoul' ;
Table 6-4 shows the result.
When you use the WHERE clause with the UPDATE statement to restrict which rows are updated, the contents of the WHERE clause can be a subselect — a SELECT statement, the result of which is used as input by another SELECT statement.
Suppose that you’re a wholesaler and your database includes a VENDOR table containing the names of all the manufacturers from whom you buy products. You also have a PRODUCT table containing the names of all the products that you sell and the prices that you charge for them. The VENDOR table has columns VendorID, VendorName, Street, City, State, and Zip. The PRODUCT table has ProductID, ProductName, VendorID, and SalePrice.
Your vendor, Cumulonimbus Corporation, decides to raise the prices of all its products by 10 percent. To maintain your profit margin, you must raise your prices on the products that you obtain from Cumulonimbus by 10 percent. You can do so by using the following UPDATE statement:
UPDATE PRODUCT SET SalePrice = (SalePrice * 1.1) WHERE VendorID IN (SELECT VendorID FROM VENDOR WHERE VendorName = 'Cumulonimbus Corporation') ;
The subselect finds the VendorID that corresponds to Cumulonimbus. You can then use the VendorID field in the PRODUCT table to find the rows that you want to update. The prices on all Cumulonimbus products increase by 10 percent; the prices on all other products stay the same.