MySQL – UPDATE with a join – IT Integrated Business Solutions

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

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

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 UPDATE each entry in the cart based on the session, automatically adding a single unit to the value of the unit in the table.

mysql> select * from sessions where sessionid = 39;
+-----------+----------------------------------+---------+---------------------+
| sessionid | session                          | loginid | expiredate          |
+-----------+----------------------------------+---------+---------------------+
|        39 | 7k3rbigiakqqlklnitbo13jbufmjjad9 |       1 | 2014-10-22 15:21:07 |
+-----------+----------------------------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from carts where sessionid = 39;
+-------+-----------+---------------+
| units | sessionid | productCodeID |
+-------+-----------+---------------+
|     1 |        39 |             1 |
|     3 |        39 |            11 |
|     5 |        39 |            12 |
+-------+-----------+---------------+
3 rows in set (0.00 sec)

The syntax for UPDATE when joining across multiple tables is:


UPDATE table1, table2
SET table1.field = value
WHERE table1.fieldn = table2.fieldn;

It is important to note that we specify the field that is going to be assigned the value in the SET clause only. Other than that the UPDATE statement takes on the same syntax as a SELECT statement.

So viewing this in action:

mysql> UPDATE carts c, sessions s
    -> SET c.units = c.units+1
    -> WHERE s.session = '7k3rbigiakqqlklnitbo13jbufmjjad9'
    -> AND s.sessionid = c.sessionid;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from carts where sessionid = 39;
+-------+-----------+---------------+
| units | sessionid | productCodeID |
+-------+-----------+---------------+
|     2 |        39 |             1 |
|     4 |        39 |            11 |
|     6 |        39 |            12 |
+-------+-----------+---------------+
3 rows in set (0.00 sec)