Search Marketing

   
   

Delete Multiple Records Across Different Database Tables

Fri May 02, 2008 9:54 pm
<<     >>
Comments: 0 Views: 1484
pdf

Deleting Records in multiple tables using MySQL can be used to completely remove certain related records completely.

The example below can be used if you have a customer or employee that needs to be completely removed from your records. You can also set up such a situation in an online application if you need to allow your users to be able to cancel their account and remove their data.

The tables below are structured simply but illustrate the basic idea of related table records in a real world example. Attached is a pdf of the database so that you can work with the same example.

1. Lets assume we have the following database table.

mysql> select * from customers;
+----+--------+
| ID | Name |
+----+--------+
| 1 | John |
| 2 | James |
| 3 | Jack |
| 4 | Jim |
| 5 | Johnna |
+----+--------+

mysql> select * from address;
+----+------------+-------+-----------+---------------+-------+-------+
| ID | CustomerID | Title | Address | City | State | Zip |
+----+------------+-------+-----------+---------------+-------+-------+
| 1 | 1 | Work | 123 Drive | NY | NY | 90210 |
| 2 | 1 | Home | 123 Drive | Trenton | NJ | 90210 |

| 3 | 2 | Work | 123 Drive | Boston | MA | 90210 |
| 4 | 2 | Home | 123 Drive | Cambridge | MA | 90210 |
| 5 | 3 | Home | 123 Drive | Philadelphia | PA | 90210 |
| 6 | 4 | Home | 123 Drive | Paris | GA | 90210 |
| 7 | 5 | Home | 123 Drive | LA | CA | 90210 |
| 8 | 5 | Work | 123 Drive | Beverly Hills | CA | 90210 |
+----+------------+-------+-----------+---------------+-------+-------+

mysql> select * from phone;
+----+------------+-------+--------------+
| ID | CustomerID | Title | Phone |
+----+------------+-------+--------------+
| 1 | 1 | Work | 555-555-5555 |
| 2 | 1 | Cell | 555-555-5555 |
| 3 | 1 | Home | 555-555-5555 |

| 4 | 2 | Work | 555-555-5555 |
| 5 | 3 | Cell | 555-555-5555 |
| 6 | 4 | Cell | 555-555-5555 |
| 7 | 5 | Cell | 555-555-5555 |
| 8 | 5 | Home | 555-555-5555 |
+----+------------+-------+--------------+


2. Lets assume we wanted to delete (completely remove) all records in our data referring to John.

mysql> DELETE FROM customers c,address a,phone p USING customers c,address a,phone p WHERE c.ID=1 && a.CustomerID=c.ID && p.CustomerID=c.ID;
Query OK, 6 rows affected (0.00 sec)

The statement above is similar to a single delete statement except that you specify what tables you are USING. Also note that you are using an INNER JOIN to specify how the records are related.

Selecting from the three tables now shows John has been removed from all of them.

mysql> select * from customers;
+----+--------+
| ID | Name |
+----+--------+
| 2 | James |
| 3 | Jack |
| 4 | Jim |
| 5 | Johnna |
+----+--------+

mysql> select * from address;
+----+------------+-------+-----------+---------------+-------+-------+
| ID | CustomerID | Title | Address | City | State | Zip |
+----+------------+-------+-----------+---------------+-------+-------+
| 3 | 2 | Work | 123 Drive | Boston | MA | 90210 |
| 4 | 2 | Home | 123 Drive | Cambridge | MA | 90210 |
| 5 | 3 | Home | 123 Drive | Philadelphia | PA | 90210 |
| 6 | 4 | Home | 123 Drive | Paris | GA | 90210 |
| 7 | 5 | Home | 123 Drive | LA | CA | 90210 |
| 8 | 5 | Work | 123 Drive | Beverly Hills | CA | 90210 |
+----+------------+-------+-----------+---------------+-------+-------+

mysql> select * from phone;
+----+------------+-------+--------------+
| ID | CustomerID | Title | Phone |
+----+------------+-------+--------------+
| 4 | 2 | Work | 555-555-5555 |
| 5 | 3 | Cell | 555-555-5555 |
| 6 | 4 | Cell | 555-555-5555 |
| 7 | 5 | Cell | 555-555-5555 |
| 8 | 5 | Home | 555-555-5555 |
+----+------------+-------+--------------+

 



Comments
Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (23 + 3)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc