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.

TABLE OF CONTENTS:

By: RK Harigopal, (c) Melonfire

Poor Best

Rating:

/ 16

July 31, 2003

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

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 >>