HomeMySQL Page 7 - Using Subqueries In MySQL (part 2)
Adjusting For Inflation - MySQL
In this concluding segment of our MySQL subquery tutorial, find out how to do more with subqueries, including check for matching values with the IN operator, check for valid result sets with the EXISTS operator, derive new "virtual" tables for use in the FROM clause of outer queries, and UPDATE and DELETE records selectively.
I could combine the operations above into the following subquery statement:
mysql> UPDATE services SET sfee = sfee + (sfee * 0.25) WHERE sid IN
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
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