How can I UPDATE the result of a SELECT statement? Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of How can I UPDATE the result of a SELECT statement? without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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.

We are here to answer your question about How can I UPDATE the result of a SELECT statement? - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji