Category: MySQL

[adsense id=”0514458240″ width=”468″ height=”60″]

Imagine you want to perform a DELETE from one table but requiring a join across multiple tables. There is a particular syntax which permits this.

In my example here, I have a sessions table which contains a session which maps to a sessionid. A cart table consists of a list of products, units and a link back to the sessionid. We wish to clear the cart based on the session provided but leave the sessions table as is i.e. without deleting the entry there.

mysql> select * from sessions where sessionid = 19;
+-----------+----------------------------------+---------+---------------------+
| sessionid | session                          | loginid | expiredate          |
+-----------+----------------------------------+---------+---------------------+
|        19 | q4f5dj4nh7kkf3ip8u98skaiinc32r14 |       1 | 2012-02-07 19:59:14 | 
+-----------+----------------------------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from carts where sessionid = 19;
+-------+-----------+---------------+
| units | sessionid | productCodeID |
+-------+-----------+---------------+
|     1 |        19 |            27 | 
|     1 |        19 |            28 | 
+-------+-----------+---------------+
2 rows in set (0.00 sec)

The syntax used to DELETE from a table is:

DELETE table_to_delete FROM table_to_delete JOIN other_tables ON (...) WHERE ...

So in our example above we have to remove based on a session ‘q4f5dj4nh7kkf3ip8u98skaiinc32r14’.

mysql> DELETE carts c FROM carts c JOIN sessions USING (sessionid) WHERE session = 'q4f5dj4nh7kkf3ip8u98skaiinc32r14';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from carts where sessionid = 19;
Empty set (0.00 sec)

mysql> select * from sessions where sessionid = 19;
+-----------+----------------------------------+---------+---------------------+
| sessionid | session                          | loginid | expiredate          |
+-----------+----------------------------------+---------+---------------------+
|        19 | q4f5dj4nh7kkf3ip8u98skaiinc32r14 |       1 | 2012-02-07 19:59:14 | 
+-----------+----------------------------------+---------+---------------------+
1 row in set (0.00 sec)