Using Subqueries In MySQL (part 2) - Adjusting For Inflation
(Page 7 of 9 )
You can use subqueries in an UPDATE statement in much the same manner. Let's suppose I wanted to find out which services are in use in 3 or more branch offices,
mysql> SELECT sid FROM branches_services GROUP BY sid HAVING COUNT(bid)
mysql> >= 3;
+-----+
| sid |
+-----+
| 1 |
| 3 |
| 4 |
+-----+
3 rows in set (0.11 sec)
and then increase the fee for those services by 25% (hey, those weekly yacht parties don't come cheap!).
mysql> UPDATE services SET sfee = sfee + (sfee * 0.25) WHERE sid = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE services SET sfee = sfee + (sfee * 0.25) WHERE sid = 3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE services SET sfee = sfee + (sfee * 0.25) WHERE sid = 4;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
I could combine the operations above into the following subquery statement:
mysql> UPDATE services SET sfee = sfee + (sfee * 0.25) WHERE sid IN
mysql> (SELECT
sid FROM branches_services GROUP BY sid HAVING COUNT(bid) >= 3); Query OK,
3 rows affected (0.22 sec) Rows matched: 3 Changed: 3 Warnings: 0
Let's take another example. Let's suppose I wanted to have all branches located in California use the "Security" service instead of the "Administration" service. With a subquery, it's a piece of cake:
mysql> UPDATE branches_services SET sid = 6 WHERE sid = 4 AND bid IN
(SELECT bid FROM branches WHERE bloc = 'CA');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
In this case, the inner query takes care of isolating only those branch IDs in California, and provides this list to the outer query, which updates the corresponding records in the "branches_services" table. Notice how I've split the selection criteria for the rows to be UPDATEd: the inner query lists the records for California, the outer one further winnows it down to those using just the "Administration" service.
Wanna make it even more complicated? Add subqueries to the various SET clauses as well.
mysql> UPDATE branches_services SET sid = (SELECT sid FROM services
mysql> WHERE
sname = 'Security') WHERE sid = (SELECT sid FROM services WHERE sname =
'Administration') AND bid IN (SELECT bid FROM branches WHERE bloc = 'CA'); Query
OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
Next: A New Broom >>
More MySQL Articles
More By RK Harigopal, (c) Melonfire
/ 14 
