Home arrow MySQL arrow 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.

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

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



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