Đang chuẩn bị liên kết để tải về tài liệu:
The Language of SQL- P40

The Language of SQL- P40:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | Correlated Subquery Updates 181 essential. Without the WHERE clause every row in the table would have been changed to Bill Smythe. Correlated Subquery Updates The previous UPDATE example is easy enough but not entirely realistic. A more common example of an UPDATE involves situations where you update data in one table based on data in another table. Let s say we have this Customers table CustomerlD State Zip 1 IL 60089 2 CA 92802 3 WI 53718 4 DC 20024 5 FL 32801 This CustomerTransactions table has the recent changes for existing customers TransactionID CustomerID State Zip 1 1 IL 60090 2 2 NV 89109 3 5 FL 32810 The Customers table is considered to be the main source of data for customers. In order to accomplish an update of the Customers table from the CustomerTransactions table we re going to need to use the correlated subquery technique discussed in Chapter 14. The correlated subquery is needed because the UPDATE statement can only specify a single table to update. We can t merely join multiple tables together and have it work. We ll need to use a correlated subquery after the SET keyword to indicate where the data comes from. The following statement can be utilized to update the State and Zip columns in the Customers table from the transactions in the CustomerTransactions table. Since this statement is fairly complex we ve inserted a few blank lines so we can subsequently discuss the four sections of the statement. 182 Chapter 17 Modifying Data UPDATE Customers SET Customers.State SELECT CustomerTransactions.State FROM CustomerTransactions WHERE CustomerTransactions.CustomerID Customers.CustomerID Customers.Zip SELECT CustomerTransactions.Zip FROM CustomerTransactions WHERE CustomerTransactions.CustomerID Customers.CustomerID WHERE EXISTS SELECT FROM CustomerTransactions WHERE CustomerTransactions.CustomerID Customers.CustomerID After executing this UPDATE the Customers table contains CustomerlD State Zip 1 IL 60090 2 NV 89109 3 WI 53562 4 MD 20814 5 FL 32810 .