Using Subqueries In MySQL (part 2) - Show Me The Money
(Page 6 of 9 )
Thus far, every single example you've seen in this tutorial has involved a subquery within an outer SELECT statement. However, subqueries can be used in other places too - most notably in UPDATE and DELETE statements, which can use the results of a subquery to constrain the records to which the UPDATE or DELETE is applied.
I'll explain this with a simple example. Let's suppose you want to delete all branches using the "Recruitment" service (service ID 2). Normally, you'd first look up the branches using that service in the "branches_services" table,
mysql> SELECT * FROM branches_services WHERE sid = 2;
+------+-----+
| bid | sid |
+------+-----+
| 1011 | 2 |
| 1031 | 2 |
+------+-----+
2 rows in set (0.16 sec)
and then delete the corresponding branch records from the "branches" table.
mysql> DELETE FROM branches WHERE bid = 1011;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM branches WHERE bid = 1031;
Query OK, 1 row affected (0.00 sec)
You can combine the two operations above into a single one with a subquery, as below:
mysql> DELETE FROM branches WHERE bid IN (SELECT bid FROM
mysql> branches_services
WHERE sid = 2);
Query OK, 2 rows affected (0.00 sec)
A check of the "branches" table confirms that the operation was successful.
mysql> SELECT * FROM branches;
+------+-----+--------------------------------+------+
| bid | cid | bdesc | bloc |
+------+-----+--------------------------------+------+
| 1012 | 101 | Accounting Department | NY |
| 1013 | 101 | Customer Grievances Department | KA |
| 1041 | 104 | Branch Office (East) | MA |
| 1042 | 104 | Branch Office (West) | CA |
| 1101 | 110 | Head Office | CA |
| 1032 | 103 | NE Region HO | CT |
| 1033 | 103 | NW Region HO | NY |
+------+-----+--------------------------------+------+
7 rows in set (0.00 sec)
How about deleting all those customers, any of whose branch offices generate service fee revenues of $500 or less?
mysql> DELETE FROM clients WHERE cid IN (SELECT DISTINCT b.cid FROM
branches AS b, branches_services AS bs, services AS s WHERE b.bid = bs.bid AND
bs.sid = s.sid GROUP BY bs.bid HAVING SUM(sfee) <= 500); Query OK, 1 row
affected (0.28 sec)
In this case, the inner query groups the various branches by branch ID, calculates the total service generated by each branch for all the services its using, and lists those records where the total is less than or equal to $500. The corresponding customer IDs are then used by the outer query to perform a DELETE operation on the "clients" table.
Next: Adjusting For Inflation >>
More MySQL Articles
More By RK Harigopal, (c) Melonfire
/ 14 
