Note: You can find all the sample statements and solutions in the “modify” version of the
respective sample databases—SalesOrdersModify, EntertainmentAgencyModify, SchoolSchedulingModify,
and BowlingLeagueModify.
“It is change, continuing change, inevitable change, that is the dominant factor in society today.”
—ISAAC ASIMOV
As you learned in Part II, “SQL Basics”; Part III, “Working with Multiple Tables”; and Part IV, “Summarizing and Grouping Data”; using the SELECT statement to fetch data from your tables can be both fun and challenging. (Okay, so maybe some of it is a lot more challenging than fun!) If all you need to do is answer questions, then you don’t need this part of my book. However, most real-world applications not only answer complex questions but also allow the user to change, add, or delete data. In addition to defining the SELECT statement that you’ve been learning about to retrieve data, the SQL Standard also defines three statements that allow you to modify your data. In this chapter, you’ll learn about the first of those statements—UPDATE.
The SELECT statement lets you retrieve sets of data from your tables. The UPDATE statement also works with sets of data, but you can use it to change the values in one or more columns and in one or more rows. By now, you should also be very familiar with expressions. To change a value in a column, you simply assign an expression to the column.
But you must be careful because UPDATE is very powerful. Most of the time you’ll want to update only one or a few rows. If you’re not careful, you can end up changing thousands of rows. To avoid this problem, I’ll show you a technique for testing your statement first.
Note: You can find all the sample statements and solutions in the “modify” version of the
respective sample databases—SalesOrdersModify, EntertainmentAgencyModify, SchoolSchedulingModify,
and BowlingLeagueModify.
The UPDATE statement is actually much simpler than the SELECT statement that you have been learning about in the previous chapters. The UPDATE statement has only three clauses: UPDATE, SET, and an optional WHERE clause, as shown in Figure 15-1.
After the UPDATE keyword, you specify the name of the table that you want to update. The SET keyword begins one or more clauses that assign a new value to a column in the table. You must include at least one assignment clause, and you can include as many assignment clauses as you need to change the value of multiple columns in each row. Use the optional WHERE clause to restrict the rows that are to be updated in the target table.
Let’s look at an example using a simple assignment of an expression to the column you want to update.
Note: Throughout this chapter, I use the “Request/Translation/Clean Up/SQL” technique introduced
in Chapter 4, “Creating a Simple Query.”
“Increase the retail price of all products by 10 percent.”
Ah, this is somewhat tricky. You’ll find it tough to directly translate your original request into SQL-like English because you don’t usually state the clauses in the same order required by the UPDATE statement. Take a close look at your request and figure out (a) the name of the target table and (b) the names of the columns you need to update. Restate your request in that order, and then proceed with the translation, like this:
“Change products by increasing the retail price by 10 percent.”
|
Translation |
Update the products table by setting the retail price equal to the retail price plus 10 percent of the price |
|
Clean Up |
Update the products table by setting the retail price equal to = the retail price plus + (.10 percent of the * retail price) |
|
|
|
Notice that you cannot say SET Price + 10 percent. You must state the column to be updated to the left of the equals sign and then
create an expression to calculate the new value you want. If the new value involves
using the old or current value of the column, then you must reference the column name
as needed to the right of the equals sign. One rule that’s very clear in the SQL Standard
is that your database system must evaluate all the assignment expressions before it updates any rows. So your database will resolve the two references to the Price
column to the right of the equals sign by fetching the value of the Price column before
it makes any changes.
You’ll find this sort of assignment statement common in any programming language. Although it might appear to you that you’re assigning the value of a column to itself, you’re really grabbing the value before it changes, adding 10 percent of the value, and then assigning the result to the column to update it to a new value.
Are you always going to want to update all rows in a table? Probably not. To limit the rows changed by your UPDATE statement, you need to add a WHERE clause. Let’s consider another problem:
“My clothing supplier just announced a price increase of 4 percent. Update the price of the clothing products and add 4 percent.”
Let’s restate that:
“Modify products by increasing the retail price by 4 percent for products that are clothing (category 3).”
|
Translation |
Update the products table by setting the retail price equal to retail price times 1.04 for all products in category 3 |
|
Clean Up |
Update the products table by setting the retail price equal to = retail price times * 1.04 for all where products in category ID = 3 |
|
|
|
Note: I simplified the calculation in the query by multiplying the original value by 1.04
rather than adding the original value to 0.04 times the original value. The result
is mathematically the same and might actually execute faster because one mathematical
operation (price times 1.04) is more efficient than two (price plus price times .04).
After tackling subqueries in Chapter 11, this was easy, right? Just wait—you’ll use subqueries extensively in your WHERE clauses, and I’ll cover that later in this chapter.
Even for simple UPDATE queries, I strongly recommend that you verify that you’re going to be updating the correct rows. How do you do that? As I mentioned, most of the time you’ll add a WHERE clause to select a subset of rows to update. Why not build a SELECT query first to return the rows that you intend to update? Continuing with my example, let’s ask the database to return a column that lets me ensure that I have the correct rows, the value I want to update, and the expression I intend to assign to the column I’m changing.
“List the product name, retail price, and retail price plus 4 percent from the products table for the products in category 3.”
|
Translation |
Select product name, retail price, and retail price times 1.04 from the products table for products in category ID 3 |
|
Clean Up |
Select product name, retail price, and retail price times * 1.04 from the products table for where products in category ID = 3 |
|
|
|
Figure 15-2 shows the result.
Note that I included the product name so I can see exactly what I want to update. If this is the result I want, I can transform the SELECT statement into the correct UPDATE statement by removing elements I don’t need and swapping the FROM and SELECT clauses. Figure 15-3 shows how to transform this SELECT statement into the correct UPDATE syntax.
Simply cross out the words you don’t need, move the table name to the UPDATE clause, move the target field and expression to the SET clause separated by an equals sign, copy your WHERE clause, and you’re done.
Before I get too much further into changing data, you need to know about an important feature available in SQL. The SQL Standard defines something called a transaction that you can use to protect a series of changes you’re making to the data in your tables. You can think of a transaction in SQL just like a transaction you might make online or at a store to buy something. You initiate the transaction when you send in your order. Paying for the item you ordered is part of the transaction. The transaction is completed when you receive and accept the merchandise. But if the merchandise doesn’t arrive, you might apply for a refund. Or if the merchandise is unsatisfactory, you return it and ask for your money back.
The SQL Standard provides three statements that mimic this scenario. You can use START TRANSACTION before you begin your changes to indicate that you want to protect and verify the changes you’re about to make. Think of this as sending in your order. You make the changes to your database—register the payment and register the receipt. If everything completes satisfactorily, you can use COMMIT to make the changes permanent. If something went wrong (the payment or receipt update failed), you can use ROLLBACK to restore the data as it was before you started the transaction.
This buying and selling example might seem silly, but transactions are a very powerful feature of SQL, especially when you need to make changes to multiple rows or to rows in several tables. Using a transaction ensures that either all changes are successful or none are. You don’t want to register the payment without receipt of the goods, and you don’t want to mark the goods received without receiving the payment. Note that this applies to changing your data not only with the UPDATE statement described in this chapter but also with INSERT and DELETE, which are described in the next two chapters.
Not all database systems implement transactions, and the syntax to use transaction processing varies slightly depending on the particular database system. Some database systems allow you to nest transactions inside each other so that you can establish multiple commit points. Some end-user database systems, such as Microsoft Office Access, start a transaction for you behind the scenes every time you run a query that changes your data. If you’ve used Microsoft Access, you know that it prompts you with a message indicating how many rows will be changed and whether any will fail—and you can either accept the changes or cancel them (ROLLBACK). As always, consult your database documentation for details.
As implied by the diagram of the UPDATE statement in Figure 15-1, you can specify more than one column to change by including additional assignment statements separated by columns. Keep in mind that your database applies all the changes you specify to every row returned as a result of evaluating your WHERE clause. Let’s take a look at an update you might want to perform in the School Scheduling database:
“Modify classes by changing the classroom to 1635 and the schedule dates from Monday-Wednesday-Friday to Tuesday-Thursday-Saturday for all drawing classes (subject ID 13).”
|
Translation |
Update classes and set classroom ID to 1635, Monday schedule to false, Wednesday schedule to false, Friday schedule to false, Tuesday schedule to true, Thursday schedule to true, and Saturday schedule to true for all classes that are subject ID 13 |
|
Update classes and set classroom ID to = 1635, Monday schedule to = 0 false, Wednesday schedule to = 0 false, Friday schedule to = 0 false, Tuesday schedule to = 1 true, Thursday schedule to = 1 true, and Saturday schedule to = 1 true for all classes that are where subject ID = 13 |
|
|
|
|
Note: Remember that most database systems use the value 0 for false and the value 1 or
–1 for true. Check your database documentation for details.
Perhaps you want to make doubly sure that you’re changing only the classes scheduled on Monday-Wednesday-Friday. To do that, add criteria to your WHERE clause like this:
|
|
|
Notice that you’re filtering for the value you expect to find in the field before your UPDATE statement changes the value. With this modified query, you’re finding all rows for SubjectID 13 that have a true (1) value in the Monday, Wednesday, and Friday schedule fields. For each row that matches these criteria, your UPDATE statement will change the ClassRoomID and the schedule fields. If you try to run this query a second time, you should find that your database updates no rows because you eliminated all rows that qualify by changing the field values the first time you ran the query.
In the examples in previous sections, I’ve updated the products in category 3 and the classes in subject 13. In the real world, code values like this don’t have much meaning. You’d probably much rather say “clothing products” or “drawing classes.” In a SELECT query, you can add the related tables to your FROM clause with JOIN specifications and then display the more meaningful value from the related table. As always, you must be familiar with your table relationships to make this connection. Figure 15-4 shows the tables I need for my example.
Let’s look again at the verification query I built to check my update of products, but this time, let’s add the Categories table:
|
|
|
Filtering on the value Clothing makes a lot more sense than selecting the category ID value 3. However, notice that the diagram of the UPDATE statement in Figure 15-1 shows that I can supply only a table name following the UPDATE keyword. I cannot specify the INNER JOIN needed to include the Categories table so that I can filter on the more meaningful value. So what’s the solution?
Remember from Chapter 11, “Subqueries,” that I can create a filter in a WHERE clause to test a value fetched from a related table. Let’s solve the price update problem again using a subquery so that I can apply a more meaningful filter value.
“Modify products by increasing the retail price by 4 percent for products that are clothing.”
|
Translation |
Update the products table by setting the retail price equal to retail price times 1.04 for the products whose category ID is equal to the selection of the category ID from the categories table where the category description is clothing |
|
Clean Up |
Update the products table by setting the retail price equal to = retail price times * 1.04 for the products whose where category ID is equal to = the (selection of the category ID from the categories table where the category description is = ‘Clothing’) |
|
|
|
That’s not as straightforward as a simple WHERE clause on a column from a joined table, but it gets the job done.
Caution: Notice that I used an equals comparison for the CategoryID column in the Products
table and the value returned by the subquery. As I noted in Chapter 11, if you want to use an equals comparison in a predicate with a subquery, the subquery
must return only one value. If more than one row in the Categories table had the value
Clothing in the category description field, this query would fail. However, in my
example, I’m reasonably certain that filtering for Clothing will return only one value for CategoryID. Whenever you’re not sure that
a subquery will return only one value, you should use the IN predicate rather than
the “equal to” operator.
Let’s solve the problem of updating classes by using the same technique. I want to use the subject code or subject name from the Subjects table rather than the numeric and meaningless subject ID. Figure 15-5 shows the tables involved.
Let’s solve the update problem again by using a subquery filter.
“Modify classes by changing the classroom to 1635 and the schedule dates from Monday-Wednesday-Friday to Tuesday-Thursday-Saturday for all drawing classes.”
|
Translation |
Update classes and set classroom ID to 1635, Monday schedule to false, Wednesday schedule to false, Friday schedule to false, Tuesday schedule to true, Thursday schedule to true, and Saturday schedule to true for all classes whose subject ID is in the selection of subject IDs from the subjects table where subject name is ‘Drawing’ |
|
Clean Up |
Update classes and set classroom ID to = 1635, Monday schedule to = 0 false, Wednesday schedule to = 0 false, Friday schedule to = 0 false, Tuesday schedule to = 1 true, Thursday schedule to = 1 true, and Saturday schedule to = 1 true for all classes whose where subject ID is in the (selection of subject IDs from the subjects table where subject name is = ‘Drawing’) |
|
|
Notice that even though I’m fairly certain that only one subject ID has a subject name equal to Drawing, I decided to play it safe and use the IN predicate.
Several database systems, most notably the ones from Microsoft (Microsoft Access and Microsoft SQL Server), allow you to specify a joined table in the FROM clause of an UPDATE query. The restriction is that the JOIN must be from the primary key in one table to the foreign key in another table so that the database system can figure out which specific row or rows you intend to update. This allows you to avoid a subquery in the WHERE clause when you want to filter rows based on a value in a related table.
If your database system allows this, you can solve the problem of modifying the information on drawing classes as follows:
|
|
|
As you can see, this avoids having to use a subquery to filter the rows. In some ways, this syntax is also easier to understand. You can also use this syntax to join a related table that supplies one of the values in your update calculation rather than use a subquery in the SET clause. Be sure to check the documentation for your database system to see if this feature is supported. You’ll note that I’ve used this technique to solve some of the sample queries in the Microsoft Access versions of the sample databases.
By the way, the SQL Standard allows the target table to be a view, which could imply a joined table. However, the standard specifies that the rules for updating a view are defined by the implementation, which allows database system vendors to either always require a simple table name or otherwise restrict what you can do using a view or joined table. As always, check your database documentation for details.
As you might imagine, you can make the subquery as complex as necessary to allow you to filter the target table properly. For example, if you want to change the start time for all classes taught by one professor, you need to JOIN the Faculty_Classes and Staff tables in the FROM clause of the subquery. Figure 15-6 shows the tables involved.
Let’s say you want to change the start time of all classes taught by Kathryn Patterson to 2:00 PM. (You probably wouldn’t want to do this because you might end up with multiple classes starting at the same time, but this makes an interesting example.) Your solution might look as follows.
“Change the classes table by setting the start time to 2:00 PM for all classes taught by Kathryn Patterson.”
|
Translation |
Update the classes table by setting the start time to 2:00 PM for all classes whose class ID is in the selection of class IDs of faculty classes joined with staff on staff ID in the faculty classes table matches staff ID in the staff table where the staff first name is ‘Kathryn’ and the staff last name is ‘Patterson’ |
|
Clean Up |
Update the classes table by setting the start time to = 2:00 PM. ‘14:00:00’ for all classes whose where class ID is in the (selection of class IDs of from faculty classes inner joined with staff on faculty_classes.staff ID in the faculty classes table matches = staff.staff ID in the staff table where the staff first name is = ‘Kathryn’ and the staff last name is = ‘Patterson’) |
|
|
|
So the trick is to identify the relationships between the target table and any related table(s) you need to specify the criteria in the WHERE clause. You did this in Chapter 8, “INNER JOINs,” and Chapter 9, “OUTER JOINs,” as you assembled the FROM clause of queries on multiple tables. When building an UPDATE statement, you can put only the target table after the UPDATE keyword, so you must take the other tables and put them in a subquery that returns the column that you can link back to the target table.
If you thought I was done using subqueries, you were wrong. Notice in Figure 15-1 that the value that you can assign to a column in a SET clause can be a value expression. Just for review, Figure 15-7 shows how to construct a value expression.
In Chapter 2, “Ensuring Your Database Structure Is Sound,” I advised you to not include calculated fields in your tables. As with most rules, there are exceptions. Consider the Orders table in the Sales Orders sample database. If your business handles extremely large orders (thousands of order detail rows), you might want to consider including an order total field in the Orders table. Including this calculated field lets you run queries to examine the total of all items ordered without having to fetch and total thousands of detail rows. If you choose to do this, you must include code in your application that keeps the calculated total up to date every time a change is made to any related order detail row.
Note: Many database systems provide a feature—often called a trigger—that enables you to run code within the database system whenever data is added, updated,
or deleted. (The act of adding, updating, or deleting data “triggers” your code.)
The trigger code can then perform additional complex validations or even run additional
update, insert, or delete queries to modify data in related tables. You can imagine
how code you write in a trigger could potentially update calculated values in related
tables.
Some database systems (notably Microsoft SQL Server) also enable you to define calculated columns as part of your table design. Clearly, such features cause your database system to do additional work whenever you work with the data in your tables, so you should carefully consider using such features and do so sparingly. Consult your database documentation for details.
So far, I’ve been assigning a literal value or a value expression containing a literal value, an operator, and a column name to columns in the SET clause. Notice that you can also assign the value of another column in the target table, but you’ll rarely want to do that. The most interesting possibility is that you can use a SELECT expression (a subquery) that returns a single value (such as a sum) from another table and assign that value to your column. You can include criteria in the subquery (a WHERE clause) that filters the values from the other table based on a value in the table you’re updating.
So, to update a total in one table (Orders) using a sum of an expression on columns in a related table (Order_Details), you can run an UPDATE query using a subquery. In the subquery, you’ll sum the value of quantity ordered times quoted price and place it in the calculated field, and you’ll add a WHERE clause to make sure you’re summing values from related rows in the Order_Details table for each row in the Orders table. Your request might look like this:
“Change the orders table by setting the order total to the sum of quantity ordered times quoted price for all related order detail rows.”
|
Translation |
Update the orders table by setting the order total to the sum of quantity ordered times quoted price from the order details table where the order number matches the order number in the orders table |
|
Clean Up |
Update the orders table by setting the order total to = the (select sum of (quantity ordered times * quoted price) from the order details table where the order_details.order number matches the = orders.order number in the orders table) |
|
|
|
Note: I saved this query as CH15_Update_Order_Totals_Subquery in the Sales Orders Modify
sample database.
Notice that I didn’t include a WHERE clause to filter the orders that the database will update. If you execute this query in application code, you’ll probably want to filter the order number so that the database updates only the order that you know was changed. Some database systems actually let you define a calculated field like this and specify how the field should be updated by your database system. As noted earlier, most database systems also support something called a trigger that the database system runs on your behalf each time a row in a specified table is changed, added, or deleted. For systems that include these features, you can use this UPDATE query syntax in either the definition of the table or in the trigger you define to run when a value changes. As usual, consult your database documentation for details.
At this point, you should have a good understanding of how to update one or more columns in a table using either a simple literal or a complex subquery expression. You also know how to filter the rows that will be changed by your UPDATE statement. The best way to give you an idea of the wide range of uses for the UPDATE statement is to list some problems you can solve with this statement and then present a set of examples in the “Sample Statements” section.
“Reduce the quoted price by 2 percent for orders shipped more than 30 days after the order date.”
“Add 6 percent to all agent salaries.”
“Change the tournament location to ‘Oasis Lanes’ for all tournaments originally scheduled at ‘Sports World Lanes.’”
“Recalculate the grade point average for all students based on classes completed.”
“Apply a 5 percent discount to all orders for customers who purchased more than $50,000 in the month of October 2017.”
“Correct the engagement contract price by multiplying the entertainer daily rate times number of days and adding a 15 percent commission.”
“Update the city and state for all bowlers by looking up the names by ZIP Code.”
“For all students and staff in ZIP codes 98270 and 98271, change the area code to 360.”
“Make sure the retail price for all bikes is at least a 45 percent markup over the wholesale price of the vendor with the lowest cost.”
“Apply a 2 percent discount to all engagements for customers who booked more than $3,000 worth of business in the month of October 2017.”
“Change the name of the ‘Huckleberrys’ bowling team to ‘Manta Rays.’”
“Increase the salary of full-time tenured staff by 5 percent.”
“Set the retail price of accessories to the wholesale price of the highest priced vendor plus 35 percent.”
“Add 0.5 percent to the commission rate of agents who have sold more than $20,000 in engagements.”
“Calculate and update the total pins, games bowled, current average, and current handicap for all bowlers.”
You now know the mechanics of constructing UPDATE queries. Let’s look at a set of samples, all of which request that one or more columns in a table be changed in some way. These examples come from four of the sample databases.
Caution: Because the sample queries you’ll find in the modified versions of the sample databases
change your data, be aware that some of the queries will work as expected only once.
For example, after you run an UPDATE query to change the name of a customer or bowling
team using a WHERE clause to find the row you want to change, subsequent attempts
to find the row to change will fail because of the change you made the first time
you ran the query. Consider restoring the databases from the sample scripts or a backup
copy if you want to work through the problems again.
Also, if you’re using MySQL, the default in the Query Editor in MySQL Workbench is to allow only “safe” updates that include specification of the Primary Key in the WHERE clause. Many of the queries shown here won’t run with that enabled. Go to Edit / Preferences / SQL Editor and clear the Safe Updates checkbox near the bottom.
I’ve also included a view of each target table before and after executing the update and a count of the number of rows that should be changed by each sample UPDATE statement. The name that appears immediately before the count of rows changed is the name I gave each query in the sample data on the companion website for the book. Also, I created a companion SELECT query (stored as a View in MySQL, PostgreSQL, and Microsoft SQL Server) for each UPDATE query that you can use to see exactly what will be changed. The name of the companion query is the name of the original query with _Query appended to the name. I stored each query in the appropriate sample database (as indicated within the example) and prefixed the names of the queries relevant to this chapter with “CH15.” You can find the sample data on the companion website for this book, www.informit.com/title/9780134858333. You can follow the instructions in the Introduction of this book to load the samples onto your computer and try them.
Note: Remember that all the column names and table names used in these examples are drawn
from the sample database structures shown in Appendix B, “Schema for the Sample Databases.” To simplify the process, I have combined the
Translation and Clean Up steps for all the examples. These samples assume you have
thoroughly studied and understood the concepts covered in previous chapters, especially
the chapter on subqueries.
All of the sample statements have a companion SELECT statement that shows the rows affected and any new values before you run the actual UPDATE statement. These additional queries (views) have the name of the actual UPDATE statement appended with _Query. For example, the UPDATE statement named CH15_Update_Order_Totals_Subquery has a companion CH15_Update_Order_Totals_Subquery_Query that simply shows you the rows affected without changing any data.
“Reduce the quoted price by 2 percent for orders shipped more than 30 days after the order date.”
Let’s restate the problem so that it more closely follows the SQL syntax.
“Change order details by setting the quoted price to quoted price times 0.98 for all orders where the shipped date is more than 30 days later than the order date.”
|
Translation/Clean Up |
Update the order details table by setting the quoted price equal to = the quoted price times * 0.98 where the order ID is in the (selection of order IDs from the orders table where ship date minus – order date is greater than > 30 |
|
|
|
Note: This query solution assumes your database system allows you to subtract one date
from another to obtain the number of days between the two dates. Consult your database
documentation for details.
Order_Details Table Before Executing the UPDATE Query – CH15_Adjust_Late_Order_Prices_Query
|
OrderNumber |
ProductNumber |
QuotedPrice |
UpdatedPrice |
|
291 |
1 |
$1,200.00 |
1176 |
|
291 |
14 |
$139.95 |
137.15 |
|
291 |
30 |
$43.65 |
42.78 |
|
9 |
$32.01 |
31.37 |
|
|
371 |
22 |
$79.54 |
77.95 |
|
371 |
35 |
$37.83 |
37.07 |
|
387 |
1 |
$1,200.00 |
1176 |
|
387 |
6 |
$635.00 |
622.3 |
|
<< more rows here >> |
|||
Order_Details Table After Executing CH15_Adjust_Late_Order_Prices (29 rows changed)
|
OrderNumber |
ProductNumber |
QuotedPrice |
QuantityOrdered |
|
291 |
1 |
$1,176.00 |
4 |
|
291 |
14 |
$137.15 |
2 |
|
291 |
30 |
$42.78 |
6 |
|
371 |
9 |
$31.37 |
6 |
|
371 |
22 |
$77.95 |
5 |
|
371 |
35 |
$37.07 |
6 |
|
387 |
1 |
$1,176.00 |
4 |
|
387 |
6 |
$622.30 |
4 |
|
<< more rows here >> |
|||
“Make sure the retail price for all bikes is at least a 45 percent markup over the wholesale price of the vendor with the lowest cost.”
Restated, the request is as follows:
“Change the products table by setting the retail price equal to 1.45 times the wholesale price of the vendor that has the lowest cost for the product where the retail price is not already equal to 1.45 times the wholesale price and the category ID is 2.”
Note: You’ll find this query solved with a JOIN in the UPDATE clause in the Microsoft Access
sample database because Access does not support a subquery that uses DISTINCT in the
SET clause. (It declares the query not updatable because of the DISTINCT.)
Notice also that the solution rounds the resulting price to the nearest dollar (zero decimal places). You’ll find that most commercial implementations support a ROUND function even though this function is not explicitly defined in the SQL Standard.
I could have also included a subquery to find the category ID that is equal to (or IN) the category IDs from the Categories table where category description is equal to Bikes, but I thought the query was complex enough without adding another subquery. Finally, I selected the DISTINCT wholesale price because more than one vendor might have the same low price. I want only one value from the subquery for the comparison.
Products Table Before Executing the UPDATE Query – CH15_Adjust_Bike_ Retail_Price_Query (1 row)
|
ProductNumber |
ProductName |
RetailPrice |
UpdatedPrice |
|
2 |
Eagle FS-3 Mountain Bike |
$1,800.00 |
1840 |
Products Table After Executing CH15_Adjust_Bike_Retail_Price (1 row changed)
|
ProductNumber |
ProductName |
RetailPrice |
|
2 |
Eagle FS-3 Mountain Bike |
$1,840.00 |
Note: If you scan the Product_Vendors table for all the bikes (product IDs 1, 2, 6, and
11), you’ll find that only product 2 has a current retail price that is less than
1.45 times the lowest wholesale price for that product from any vendor. The wholesale
price for bike 2 from vendor ID 6 is $1,269, and 1.45 times this amount is $1,840.05,
which the query rounded to the nearest dollar.
Restated, the request is as follows:
“Change the agents table by adding 6 percent to all salaries.”
|
Translation/Clean Up |
Update the agents table by setting salary equal to = salary times * 1.06 |
|
|
|
Note: I’ve again used the common ROUND function found in most commercial implementations
and have specified rounding to zero decimal places. Check your database system documentation
for specific details about rounding in your implementation.
Agents Table Before Executing the UPDATE Query – CH15_Give_Agents_ 6Percent_Raise_Query (9 rows)
|
AgentID |
AgtFirstName |
AgtLastName |
Salary |
NewSalary |
|
1 |
William |
Thompson |
$35,000.00 |
37100 |
|
2 |
Scott |
Bishop |
$27,000.00 |
28620 |
|
3 |
Carol |
Viescas |
$30,000.00 |
31800 |
|
4 |
Karen |
Smith |
$22,000.00 |
23320 |
|
5 |
Marianne |
Wier |
$24,500.00 |
25970 |
|
6 |
John |
Kennedy |
$33,000.00 |
34980 |
|
7 |
Caleb |
Viescas |
$22,100.00 |
23426 |
|
8 |
Maria |
Patterson |
$30,000.00 |
31800 |
|
9 |
Daffy |
Dumbwit |
$50.00 |
53 |
Agents Table After Executing CH15_Give_Agents_6Percent_Raise (9 rows changed)
|
AgentID |
AgtFirstName |
AgtLastName |
Salary |
|
1 |
William |
Thompson |
$37,100.00 |
|
2 |
Scott |
Bishop |
$28,620.00 |
|
3 |
Carol |
Viescas |
$31,800.00 |
|
4 |
Karen |
Smith |
$23,320.00 |
|
5 |
Marianne |
Wier |
$25,970.00 |
|
6 |
John |
Kennedy |
$34,980.00 |
|
7 |
Caleb |
Viescas |
$23,426.00 |
|
8 |
Maria |
Patterson |
$31,800.00 |
|
9 |
Daffy |
Dumbwit |
$53.00 |
“Correct the engagement contract price by multiplying the entertainer daily rate by the number of days and adding a 15 percent commission.”
Let’s restate that:
“Modify the engagements table by setting the contract price to 1.15 times the number of days for the contract times the entertainer daily rate.”
|
Translation/Clean Up |
Update the engagements table by setting the contract price equal to = 1.15 times * the (end date minus – the start date plus + 1) and then times the * (selection of the entertainer price per day from the entertainers table where the entertainers table entertainer ID is equal to = the engagements table entertainer ID |
|
|
|
Note: This query solution assumes your database system allows you to subtract one date
from another to obtain the number of days between the two dates. Consult your database
documentation for details.
I add 1 to the difference to obtain the actual number of days because the entertainment occurs on both the first and the last days of the engagement. It’s clear you need to do this for an engagement that is booked for only one day. The start and end days are the same, so the difference is zero, but the engagement played for exactly one day.
Microsoft Access uses “banker’s rounding” on fractions equal to exactly 0.5, so the result is rounded to the nearest even number. For example, 3.5 rounds to 4, and 2.5 rounds to 2. This can make some results different by one dollar.
Entertainer Prices per Day
|
EntertainerID |
EntStageName |
EntPricePerDay |
|
1001 |
Carol Peacock Trio |
$175.00 |
|
1002 |
Topazz |
$120.00 |
|
1003 |
JV & the Deep Six |
$275.00 |
|
1004 |
Jim Glynn |
$60.00 |
|
1005 |
Jazz Persuasion |
$125.00 |
|
1006 |
Modern Dance |
$250.00 |
|
1007 |
Coldwater Cattle Company |
$275.00 |
|
1008 |
Country Feeling |
$280.00 |
|
1009 |
Katherine Ehrlich |
$145.00 |
|
1010 |
Saturday Revue |
$250.00 |
|
1011 |
Julia Schnebly |
$90.00 |
|
1012 |
Susan McLain |
$75.00 |
|
1013 |
Caroline Coie Cuartet |
$250.00 |
Engagements Table Before Executing the UPDATE Query – CH15_Calculate_ Entertainment_ContractPrice_Query (111 rows)
|
Engagement Number |
ContractPrice |
NewContractPrice |
EntertainerID |
|
2 |
$200.00 |
345 |
1004 |
|
3 |
$590.00 |
863 |
1005 |
|
4 |
$470.00 |
483 |
1004 |
|
5 |
$1,130.00 |
1265 |
1003 |
|
6 |
$2,300.00 |
1610 |
1008 |
|
7 |
$770.00 |
1104 |
1002 |
|
8 |
$1,850.00 |
2530 |
1007 |
|
9 |
$1,370.00 |
3163 |
1010 |
|
10 |
$3,650.00 |
3163 |
1003 |
|
<< more rows here >> |
|||
Engagements Table After Executing CH15_Calculate_Entertainment_ContractPrice (111 rows changed)
|
EngagementNumber |
Contract Price |
EntertainerID |
|
2 |
$345.00 |
1004 |
|
3 |
$862.00 |
1005 |
|
4 |
$483.00 |
1004 |
|
5 |
$1,265.00 |
1003 |
|
6 |
$1,610.00 |
1008 |
|
7 |
$1,104.00 |
1002 |
|
8 |
$2,530.00 |
1007 |
|
9 |
$3,162.00 |
1010 |
|
10 |
$3,162.00 |
1003 |
|
<< more rows here >> |
||
Note: The original contract price values in the Engagements table are simply random values
within a reasonable range that I chose when I created the original sample data. This
update query clearly corrects each value to a more reasonable charge based on each
entertainer’s daily rate.
“For all students in ZIP Codes 98270 and 98271, change the area code to 360.”
Restated, the problem is as follows:
“Change the students table by setting the area code to 360 for all students who live in ZIP Codes 98270 and 98271.”
|
Translation/Clean Up |
Update the students table by setting the area code equal to = ‘360’ where the student zip code is in the list (‘98270’, and ‘98271’) |
|
|
|
Students Table Before Executing the UPDATE Query CH15_Fix_Student_ AreaCode_Query (2 rows)
|
Student ID |
Stud FirstName |
Stud LastName |
Stud ZipCode |
Stud AreaCode |
NewStud AreaCode |
|
1007 |
Elizabeth |
Hallmark |
98271 |
253 |
360 |
|
1017 |
George |
Chavez |
98270 |
206 |
360 |
Students Table After Executing CH15_Fix_Student_AreaCode (2 rows changed)
|
StudentID |
StudFirstName |
StudLastName |
StudZipCode |
StudAreaCode |
|
1001 |
Kerry |
Patterson |
78284 |
210 |
|
1007 |
Elizabeth |
Hallmark |
98271 |
360 |
|
1008 |
Sara |
Sheskey |
97208 |
503 |
|
<< more rows here >> |
||||
|
Steve |
Pundt |
75204 |
972 |
|
|
1017 |
George |
Chavez |
98270 |
360 |
|
1018 |
Richard |
Lum |
98115 |
206 |
|
1019 |
Daffy |
Dumbwit |
98002 |
425 |
“Recalculate the grade point average for all students based on classes completed.”
Restated, the request looks like this:
“Modify the students table by setting the grade point average to the sum of the credits times the grade divided by the sum of the credits.”
|
Translation/Clean Up |
Update the students table by setting the student GPA equal to = the (selection of the sum of (credits times * grade) divided by / the sum of (credits) from the classes table inner joined with the student schedules table on classes.class ID in the classes table matches = student_schedules.class ID in the student schedules table where the class status is = complete 2 and the student schedules table student ID is equal to = the students table student ID) |
|
|
|
Students Table Before Executing the UPDATE Query – CH15_Update_Student_ GPA_Query (19 rows)
|
StudentID |
StudFirstName |
StudLastName |
StudGPA |
NewStudGPA |
|
|
1001 |
Kerry |
Patterson |
74.465 |
81.075 |
|
|
1002 |
David |
Hamilton |
78.755 |
80.09 |
|
|
1003 |
Betsy |
Stadick |
85.235 |
80.31 |
|
|
1004 |
Janice |
Galvin |
81 |
85.042 |
|
|
1005 |
Doris |
Hartwig |
72.225 |
85.135 |
|
|
1006 |
Scott |
Bishop |
88.5 |
77.512 |
|
|
1007 |
Elizabeth |
Hallmark |
87.65 |
72.098 |
|
|
1008 |
Sara |
Sheskey |
84.625 |
85.695 |
|
|
<< more rows here >> |
|||||
Students Table After Executing the CH15_Update_Student_GPA Query (19 rows changed)
|
StudentID |
StudFirstName |
StudLastName |
StudGPA |
|
1001 |
Kerry |
Patterson |
81.075 |
|
1002 |
David |
Hamilton |
80.09 |
|
1003 |
Betsy |
Stadick |
80.31 |
|
1004 |
Janice |
Galvin |
85.042 |
|
1005 |
Doris |
Hartwig |
85.135 |
|
1006 |
Scott |
Bishop |
77.512 |
|
1007 |
Elizabeth |
Hallmark |
72.098 |
|
1008 |
Sara |
Sheskey |
85.695 |
|
<< more rows here >> |
|||
Note: Because Microsoft Access does not support using subqueries with aggregate functions,
you’ll find this query solved as a series of calls to built-in functions using a predefined
view on the Student_Schedules and Classes tables. Also, if you use the SQL shown above,
you will get a Null result for the last student who hasn’t registered for any classes. In all four sample databases, I avoid the Null and substitute a 0 value
using functions available in each database system. In Chapter 19, “Condition Testing,” I’ll show you how to avoid this problem using CASE.
“Calculate and update the total pins, games bowled, current average, and current handicap for all bowlers.”
Note: You calculated the handicap using a SELECT query in the “Problems for You to Solve”
section of Chapter 13, “Grouping Data.” For a hint, see the CH13_Bowler_Average_Handicap query in the Bowling
League sample database. Remember that the handicap is 90 percent of 200 minus the
bowler’s average.
Let’s restate the problem like this:
“Modify the bowlers table by calculating the total pins, games bowled, current average, and current handicap from the bowler scores table.”
|
Translation/Clean Up |
Update the bowlers table by setting the total pins equal to = the (selection of the sum of the (raw score) from the bowler scores table where the bowler scores table bowler ID is equal to = the bowlers table bowler ID), and the games bowled equal to = the (selection of the count of the (raw score) from the bowler scores table where the bowler scores table bowler ID is equal to = the bowlers table bowler ID), and the current average equal to = the (selection of the average avg of the (raw score) from the bowler scores table where the bowler scores table bowler ID is equal to = the bowlers table bowler ID), and the current handicap equal to = the (selection of 0.9 times * (200 minus – the average avg of the (raw score)) from the bowler scores table where the bowler scores table bowler ID is equal to = the bowlers table bowler ID) |
|
|
Bowlers Table Before Executing the UPDATE Query – CH15_Calc_Bowler_Pins_ Avg_Hcp_Query (34 rows)
|
Bowler ID |
Bowler Total Pins |
New Bowler Total Pins |
Bowler Games Bowled |
New Bowler Games Bowled |
Bowler Current Average |
New Bowler Current Average |
Bowler Current Hcp |
New Bowler Current Hcp |
|
1 |
5790 |
6242 |
39 |
42 |
148 |
149 |
47 |
46 |
|
2 |
6152 |
6581 |
39 |
42 |
158 |
157 |
38 |
39 |
|
3 |
6435 |
6956 |
39 |
42 |
165 |
166 |
32 |
31 |
|
4 |
5534 |
5963 |
39 |
42 |
142 |
142 |
52 |
52 |
|
5 |
5819 |
6269 |
39 |
42 |
149 |
149 |
46 |
46 |
|
6 |
6150 |
6654 |
39 |
42 |
158 |
158 |
38 |
38 |
|
6607 |
7042 |
39 |
42 |
169 |
168 |
28 |
29 |
|
|
8 |
5558 |
5983 |
39 |
42 |
143 |
142 |
51 |
52 |
|
9 |
5874 |
6319 |
39 |
42 |
151 |
150 |
44 |
45 |
|
10 |
6184 |
6702 |
39 |
42 |
159 |
160 |
37 |
36 |
|
<< more rows here >> |
||||||||
Bowlers Table After Executing CH15_Calc_Bowler_Pins_Avg_Hcp (34 rows changed)
|
BowlerID |
<<other columns>> |
Bowler TotalPins |
Bowler GamesBowled |
Bowler CurrentAverage |
Bowler CurrentHcp |
|||
|
1 |
… |
6242 |
42 |
149 |
46 |
|||
|
2 |
… |
6581 |
42 |
157 |
39 |
|||
|
3 |
… |
6956 |
42 |
166 |
31 |
|||
|
4 |
… |
5963 |
42 |
142 |
52 |
|||
|
5 |
… |
6269 |
42 |
149 |
46 |
|||
|
6 |
… |
6654 |
42 |
158 |
38 |
|||
|
7 |
… |
7042 |
42 |
168 |
29 |
|||
|
8 |
… |
5983 |
42 |
142 |
52 |
|||
|
9 |
… |
6319 |
42 |
150 |
45 |
|||
|
10 |
… |
6702 |
42 |
160 |
36 |
|||
|
<< more rows here >> |
||||||||
Note: Because Microsoft Access does not support using subqueries with aggregate functions,
you’ll find this query solved as a series of calls to built-in functions. Also, if
you use the SQL shown above, you will get a Null result for the last two bowlers who
haven’t bowled any games. In all four sample databases, I avoid the Null and substitute
a 0 value using functions available in each database system. In Chapter 19, I’ll show you how to avoid this problem using CASE.
Restated, the problem is as follows:
“Modify the tournaments table by changing the tournament location to ‘Oasis Lanes’ for all tournaments originally scheduled at ‘Sports World Lanes.’”
|
Translation/Clean Up |
Update the tournaments table by setting the tourney location equal to = ‘Oasis Lanes’ where the original tourney location is equal to = ‘Sports World Lanes’ |
|
|
|
Tournaments Table Before Executing the UPDATE Query – CH15_Change_ Tourney_Location_Query (3 rows)
|
TourneyID |
TourneyLocation |
NewTourneyLocation |
|
5 |
Sports World Lanes |
Oasis Lanes |
|
12 |
Sports World Lanes |
Oasis Lanes |
|
18 |
Sports World Lanes |
Oasis Lanes |
Tournaments Table After Executing CH15_Change_Tourney_Location (3 rows changed)
|
TourneyID |
TourneyDate |
TourneyLocation |
|
<< more rows here >> |
||
|
5 |
2017-10-02 |
Oasis Lanes |
|
<< more rows here >> |
||
|
12 |
2017-11-20 |
Oasis Lanes |
|
<< more rows here >> |
||
|
18 |
2018-08-02 |
Oasis Lanes |
|
19 |
2018-08-09 |
Imperial Lanes |
|
20 |
2018-08-16 |
Totem Lanes |
I started the chapter with a brief discussion about the UPDATE statement used to change data in tables rather than to fetch data. I introduced the syntax of the UPDATE statement and explained a simple example to update one column in all the rows in a table using an expression.
Next, I showed an example of how to use the WHERE clause to filter the rows you are updating. I also showed you how to construct a SELECT query first to verify that you’ll be updating the correct rows, and I showed you how to map the clauses in your SELECT query into the UPDATE statement you need. Next, I explained the importance of transactions and how you can use them to protect against errors or to ensure that either all changes or no changes are made to your tables. I continued my discussion by showing you how to update multiple columns in a table with a single UPDATE query.
Then I entered the realm of using subqueries in your UPDATE queries. I explained how to use a subquery to create a more complex filter in your WHERE clause. Finally, I showed you how to use a subquery to generate a new value to assign to a column in your SET clause. The rest of the chapter provided examples of how to build UPDATE queries.
The following section presents several problems that you can work out on your own.
Below, I show you the request statement and the name of the solution query in the sample databases. If you want some practice, you can work out the SQL you need for each request and then check your answer with the query I saved in the samples. Don’t worry if your syntax doesn’t exactly match the syntax of the queries I saved—as long as your result is the same.
1. “Apply a 5 percent discount to all orders for customers who purchased more than $50,000 in the month of October 2017.”
(Hint: You need a subquery within a subquery to fetch the order numbers for all orders where the customer ID of the order is in the set of customers who ordered more than $50,000 in the month of October.)
You can find the solution in CH15_Give_Discount_To_Good_ October_Customers (639 rows changed). Be sure to run CH15_Update_Order_Totals_Subquery to correct the totals in the Orders table after executing this query.
2. “Set the retail price of accessories (category = 1) to the wholesale price of the highest-priced vendor plus 35 percent.”
(Hint: See CH15_Adjust_Bike_Retail_Price in the Sample Statements for the technique.)
You can find the solution in CH15_Adjust_Accessory_Retail_Price (11 rows changed).
1. “Apply a 2 percent discount to all engagements for customers who booked more than $3,000 worth of business in the month of October 2017.”
(Hint: Use an aggregate subquery to find those customers with engagements in October HAVING total bookings greater than $3,000.)
You can find the solution in CH15_Discount_Good_Customers_October (34 rows changed).
2. “Add 0.5 percent to the commission rate of agents who have sold more than $20,000 in engagements.”
(Hint: Use an aggregate subquery to find those agents HAVING total bookings greater than $20,000.)
You can find the solution in CH15_Reward_Good_Agents (3 rows changed).
1. “Increase the salary of full-time tenured staff by 5 percent.”
(Hint: Use a subquery in the WHERE clause to find matching staff IDs in the faculty table that have a status of full-time and a tenured field value of true, that is, 1 or –1, depending on your database system.)
You can find the solution in CH15_Give_FullTime_Tenured_Raise (21 rows changed).
2. “For all staff in zip codes 98270 and 98271, change the area code to 360.”
You can find the solution in CH15_Fix_Staff_AreaCode (2 rows changed).
1. “Change the name of the ‘Huckleberrys’ bowling team to ‘Manta Rays.’”
You can find the solution in CH15_Change_Huckleberry_Name (1 row changed).
2. “Update the city and state for all bowlers by looking up the names by ZIP Code.”
(Hint: Use a subquery to fetch the matching city name and another subquery to fetch the matching state from the WAZips table.)
You can find the solution in CH15_Update_Bowler_City_State (6 rows changed).
