How can I update two different tables in one query?

Question

I have two tables like this:

// users
+----+--------+----------------------------+------------------+
| id |  name  |            email           | notification_num |
+----+--------+----------------------------+------------------+
| 1  | John   | John134@gmail.com          | 4                |
| 2  | Peter  | matrix_peter@ymail.com     | 2                |
| 3  | Jack   | jk_43m@gmail.com           | 6                |
+----+--------+----------------------------+------------------+

// notifications
+----+---------+---------+------+
| id | post_id | user_id | seen |
+----+---------+---------+------+
| 1  | 4325    | 2       | NULL |
| 2  | 8765    | 3       | NULL | 
| 3  | 5454    | 3       | 1    |
| 4  | 4346    | 2       | NULL |
| 5  | 3243    | 1       | 1    |
| 6  | 3246    | 2       | 1    |
| 7  | 3764    | 3       | 1    |
+----+---------+---------+------+

Ok, All I have is just a user_id. For example :user_id = 2, And then I want this output:

// users
+----+--------+----------------------------+------------------+
| id |  name  |            email           | notification_num |
+----+--------+----------------------------+------------------+
| 1  | John   | John134@gmail.com          | 4                |
| 2  | Peter  | matrix_peter@ymail.com     | 0                |
| 3  | Jack   | jk_43m@gmail.com           | 6                |
+----+--------+----------------------------+------------------+

// notifications
+----+---------+---------+------+
| id | post_id | user_id | seen |
+----+---------+---------+------+
| 1  | 4325    | 2       | 1    |
| 2  | 8765    | 3       | NULL | 
| 3  | 5454    | 3       | 1    |
| 4  | 4346    | 2       | 1    |
| 5  | 3243    | 1       | 1    |
| 6  | 3246    | 2       | 1    |
| 7  | 3764    | 3       | 1    |
+----+---------+---------+------+

As you see, I want to update users.cotification_num and set it to 0 (for such an user), and I also want to update notifications.seen and set all rows to 1 (for such an user). How can I do that?

I can do that by two different queries like this:

UPDATE users SET notification_num = 0 WHERE id = :user_id

UPDATE notifications SET seen = 1 WHERE seen is NULL and user_id = :user_id

But I want to know how can I do that by one query?

score 2 · Accepted Answer · answered Aug 29 '16 at 23:09

2

You can use a multiple-table UPDATE:

UPDATE users u LEFT JOIN notifications n ON n.user_id = u.id
SET    u.notification_num = 0,
       n.seen = 1
WHERE  u.id = 2

answered Aug 29 '16 at 23:09

eggyal

122,705
18
212
237

Just one thing, Why `LEFT JOIN`? – Martin AJ Aug 29 '16 at 23:14
@MartinAJ: If there are no records in the `notifications` table for that user, an inner join would fail to update the `users` table whereas this outer join will still do that. – eggyal Aug 30 '16 at 06:01
Ah, what do you mean "outer join"? Are both `outer join` and `left join` the same? – Martin AJ Aug 30 '16 at 21:25
@MartinAJ: LEFT JOIN is one of three types of outer join (RIGHT JOIN and FULL JOIN being the other two, although MySQL doesn't support the latter); in each case the keyword OUTER can be optionally inserted before the word JOIN, eg LEFT OUTER JOIN etc. See Jeff Attwood's excellent article [A Visual Explanation of SQL Joins](https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) for a good explanation of the difference. – eggyal Aug 30 '16 at 21:29
got it, thx .. just you know, still I'm doubt to choose either `INNER JOIN` or `LEFT JOIN`. You know, I have a trigger *(after insert)* on `notifications` table which increases `notification_num` column of `users` table. So in general, always there is row(s) in `notifications` table *(for that user)* if `users.notification_num` isn't `0`. Still do you think I need `LEFT JOIN`? – Martin AJ Aug 30 '16 at 21:36
1

@MartinAJ: In that case no, you could also use INNER JOIN. No harm in having an outer join though. – eggyal Aug 30 '16 at 21:39

score 2 · Answer 2 · edited May 23 '17 at 12:22

2

You can join the tables together to UPDATE both in a single query:

UPDATE users, notifications
SET users.notification_num = 0
  ,  notification.seen = 1
WHERE users.id = notifications.user_id AND
      users.id = 2 AND
      notifications.seen is NULL

Read here for more information. Another option would be to simply do 2 separate updates within a transaction:

START TRANSACTION;
UPDATE users SET notification_num = 0 WHERE id = 2;
UPDATE notifications SET seen = 1 WHERE user_id = 2 AND notifications.seen is NULL;
COMMIT;

edited May 23 '17 at 12:22

Community

1
1

answered Aug 29 '16 at 23:09

Tim Biegeleisen

502,043
27
286
360

The IS NULL bit seems like it might be redundant. – Strawberry Aug 29 '16 at 23:23
1

@Strawberry well absolutely not ..! `... AND seen IS NULL` avoids to re-update the rows that have `seen=1` already. – Martin AJ Aug 29 '16 at 23:30
1

@MartinAJ Updated per your comment, thanks for the clarification. – Tim Biegeleisen Aug 29 '16 at 23:36
@MartinAJ it makes no difference. MySQL won't update rows it doesn't need to – Strawberry Aug 29 '16 at 23:55
@Strawberry interesting, I didn't know that. – Martin AJ Aug 30 '16 at 00:13

score 1 · Answer 3 · answered Aug 29 '16 at 23:26

Note that my schema is very slightly different from yours - but it makes very little difference...

 DROP TABLE IF EXISTS users;

CREATE TABLE users 
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name  VARCHAR(12) NOT NULL
,email VARCHAR(20) NOT NULL
,notification_num INT NOT NULL
);

INSERT INTO users VALUES
(1,'John','John134@gmail.com',4),
(2,'Peter','matrix_peter@ymail.com',2),
(3,'Jack','jk_43m@gmail.com',6);

DROP TABLE IF EXISTS notifications;

CREATE TABLE notifications 
(post_id INT NOT NULL
,user_id INT NOT NULL
,seen TINYINT DEFAULT 0
,PRIMARY KEY(post_id,user_id)
);

INSERT INTO notifications VALUES
(4325,2,0),
(8765,3,0),
(5454,3,1),
(4346,2,0),
(3243,1,1),
(3246,2,1),
(3764,3,1);

 SELECT * FROM notifications;
 +---------+---------+------+
 | post_id | user_id | seen |
 +---------+---------+------+
 |    3243 |       1 |    1 |
 |    3246 |       2 |    1 |
 |    3764 |       3 |    1 |
 |    4325 |       2 |    0 |
 |    4346 |       2 |    0 |
 |    5454 |       3 |    1 |
 |    8765 |       3 |    0 |
 +---------+---------+------+

 SELECT * FROM users;
 +----+-------+----------------------+------------------+
 | id | name  | email                | notification_num |
 +----+-------+----------------------+------------------+
 |  1 | John  | John134@gmail.com    |                4 |
 |  2 | Peter | matrix_peter@ymail.c |                2 |
 |  3 | Jack  | jk_43m@gmail.com     |                6 |
 +----+-------+----------------------+------------------+


UPDATE users u 
  JOIN notifications n 
    ON n.user_id = u.id
   SET u.notification_num = 0, n.seen = 1
 WHERE u.id = 2;

 SELECT * FROM users;
  +----+-------+----------------------+------------------+
  | id | name  | email                | notification_num |
  +----+-------+----------------------+------------------+
  |  1 | John  | John134@gmail.com    |                4 |
  |  2 | Peter | matrix_peter@ymail.c |                0 |
  |  3 | Jack  | jk_43m@gmail.com     |                6 |
  +----+-------+----------------------+------------------+

  SELECT * FROM notifications;
  +---------+---------+------+
  | post_id | user_id | seen |
  +---------+---------+------+
  |    3243 |       1 |    1 |
  |    3246 |       2 |    1 |
  |    3764 |       3 |    1 |
  |    4325 |       2 |    1 |
  |    4346 |       2 |    1 |
  |    5454 |       3 |    1 |
  |    8765 |       3 |    0 |
  +---------+---------+------+

I cannot see any difference between your schema and mine .. thank you anyway. upvote. Just onething, why not `LEFT JOIN` ? — Martin AJ, Aug 29 '16 at 23:30
There's no surrogate key on notifications and no null on `seen`. why left join? Seems like an extremely odd idea to me! — Strawberry, Aug 29 '16 at 23:51
In this context, NULL means something like 'I don't know whether the message has been seen or not'. I can't see how that's useful. Surely it's better to say something like 'I assume that a message has not been seen, unless I know that it has'. 0 reflects that logic better. — Strawberry, Aug 30 '16 at 21:34

How can I update two different tables in one query?

3 Answers3