Stack Stack - 6 months ago 11
MySQL Question

How can I UPDATE the result of a SELECT statement?

I have a table like this:

// notifications
+----+--------------+------+---------+------------+
| id | event | seen | id_user | time_stamp |
+----+--------------+------+---------+------------+
| 1 | vote | 1 | 123 | 1464174617 |
| 2 | comment | 1 | 456 | 1464174664 |
| 3 | vote | 1 | 123 | 1464174725 |
| 4 | answer | 1 | 123 | 1464174813 |
| 5 | comment | NULL | 456 | 1464174928 |
| 6 | comment | 1 | 123 | 1464175114 |
| 7 | vote | NULL | 456 | 1464175317 |
| 8 | answer | NULL | 123 | 1464175279 |
| 9 | vote | NULL | 123 | 1464176618 |
+----+--------------+------+---------+------------+


And here is my query:

(SELECT id, event, seen, time_stamp
FROM notifications n
WHERE id_user = :id AND seen IS NULL
)UNION
(SELECT id, event, seen, time_stamp
FROM notification n
WHERE id_user = :id AND seen IS NOT NULL
LIMIT 2
)UNION
(SELECT id, event, seen, time_stamp
FROM notifications n
WHERE id_user = :id
ORDER BY (seen IS NULL) desc, time_stamp desc
LIMIT 15
)
ORDER BY (seen IS NULL) desc, time_stamp desc;





Now I'm trying to update matched rows from query above and set
seen = 1
, Something like this:

UPDATE notifications SET seen = 1 WHERE /* the result of query above */


How can I do that?




Note: I also use PHP and PDO to execute that SELECT query (If it is important to know)

$stm = $db->prepare(" (SELECT id, ... ");
$stm->execute( /* passing some variables */);
$stm->fetchAll(PDO::FETCH_ASSOC);

Answer

seen can only be 1 or null. In that case, most of what you are getting from that UNION query is irrelevant. It seems to me that the end result of the update you're trying to do will be:

UPDATE notifications SET seen = 1 WHERE id_user = :id AND seen IS NULL

Anything else will be updating something that's already 1 to 1.

Comments