Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

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.

Name City Area Code Telephone
Abe Abelson Springfield (714) 555-1111
Bill Bailey Decatur (714) 555-2222
Chuck Wood Philo (714) 555-3333
Don Stetson Philo (714) 555-4444
Dolph Stetson Philo (714) 555-5555

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.

Name City Area Code Telephone
Abe Abelson Kankakee (714) 666-6666
Bill Bailey Decatur (714) 555-2222
Chuck Wood Philo (714) 555-3333
Don Stetson Philo (714) 555-4444
Dolph Stetson Philo (714) 555-5555

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.

Name City Area Code Telephone
Abe Abelson Kankakee (714) 666-6666
Bill Bailey Decatur (714) 555-2222
Chuck Wood Philo (619) 555-3333
Don Stetson Philo (619) 555-4444
Dolph Stetson Philo (619) 555-5555

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.

Name City Area Code Telephone
Abe Abelson Rantoul (714) 666-6666
Bill Bailey Rantoul (714) 555-2222
Chuck Wood Rantoul (619) 555-3333
Don Stetson Rantoul (619) 555-4444
Dolph Stetson Rantoul (619) 555-5555

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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!