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

A New Broom - 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

Now for something a little off the beaten track. Let's suppose I'm on a "let's-clean-up-the-database" binge, and I need to check if the various tables are in sync with each other. For example, let's see if, for every customer, there exists at least one branch office in the "branches" table. The quickest way to do this is with a left join and an eyeball check for NULL values.




mysql> SELECT clients.cid, clients.cname, branches.bid, branches.bdesc
mysql> FROM
clients LEFT JOIN branches USING (cid);
+-----+-----------------------------+------+--------------------------------+
| cid | cname | bid | bdesc |
+-----+-----------------------------+------+--------------------------------+
| 101 | JV Real Estate | 1011 | Corporate HQ |
| 101 | JV Real Estate | 1012 | Accounting Department |
| 101 | JV Real Estate | 1013 | Customer Grievances Department |
| 102 | ABC Talent Agency | NULL | NULL |
| 103 | DMW Trading | 1031 | N Region HO |
| 103 | DMW Trading | 1032 | NE Region HO |
| 103 | DMW Trading | 1033 | NW Region HO |
| 104 | Rabbit Foods Inc | 1041 | Branch Office (East) |
| 104 | Rabbit Foods Inc | 1042 | Branch Office (West) |
| 110 | Sharp Eyes Detective Agency | 1101 | Head Office |
+-----+-----------------------------+------+--------------------------------+
10 rows in set (0.00 sec)

Hmmm. It's fairly obvious that we have a record for the customer "ABC Talent Agency", but no corresponding record for one or more branch offices. Since I've decided this is a major offense, I can nuke the client record with a fast DELETE using the customer ID,


mysql> DELETE FROM clients WHERE cid = 102;
Query OK, 1 row affected (0.01 sec)

or I could combine the two steps above into one using the following subquery:


mysql> DELETE FROM clients WHERE cid = (SELECT clients.cid FROM clients
LEFT JOIN branches USING (cid) WHERE bid IS NULL);
ERROR 1093: You can't specify target table 'clients' for update in FROM clause

Interesting. Do you know why?

It's actually pretty simple (and even makes sense when you think about it). MySQL won't let you delete or update a table's data if you're simultaneously reading that same data with a subquery, as doing so opens up a window where your subquery might reference rows which have already been deleted or altered. Therefore, the table named in an outer DELETE or UPDATE statement cannot appear in the FROM clause of an inner subquery (which is what MySQL more tersely said in its error message above).

Thus, as per the SQL standard, the query above is actually illegal, and should be rewritten using an EXISTS test as follows:


mysql> DELETE FROM clients WHERE NOT EXISTS (SELECT * FROM branches
mysql> WHERE
branches.cid = clients.cid);
Query OK, 1 row affected (0.11 sec)

A quick glance at the "clients" table will reveal that the offending entry has now been removed.


mysql> SELECT * FROM clients;
+-----+-----------------------------+
| cid | cname |
+-----+-----------------------------+
| 101 | JV Real Estate |
| 103 | DMW Trading |
| 104 | Rabbit Foods Inc |
| 110 | Sharp Eyes Detective Agency |
+-----+-----------------------------+
4 rows in set (0.22 sec)



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