Home arrow MySQL arrow Page 6 - Using Subqueries In MySQL (part 2)

Show Me The Money - 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:
  1. Using Subqueries In MySQL (part 2)
  2. Total Recall
  3. In And Out
  4. A Solitary Existence
  5. Turning The Tables
  6. Show Me The Money
  7. Adjusting For Inflation
  8. A New Broom
  9. Cleaning Up
By: RK Harigopal, (c) Melonfire
Rating: starstarstarstarstar / 16
July 31, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By RK Harigopal, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: