CODEWITHSUNDEEP
mysql
skiy
skiy

Reputation: 3

mysql update column value from another table

post

+-----+-----+-----+
| tid | uid | pid |
+-----+-----+-----+
|   1 |   1 |   1 |
|   2 |   1 |   2 |
|   3 |   1 |   3 |
|   3 |   2 |   4 |
|   4 |   1 |   5 |
...
+-----+-----+-----+

thread

+-----+---------+---------+
| tid | lastpid | lastuid |
+-----+---------+---------+
|   1 |       0 |       0 |
|   2 |       0 |       0 |
|   3 |       0 |       0 |
|   4 |       0 |       0 |
...
+-----+---------+---------+

I hope the thread's result is:

+-----+---------+---------+
| tid | lastpid | lastuid |
+-----+---------+---------+
|   1 |       1 |       1 |
|   2 |       1 |       2 |
|   3 |       2 |       4 |
...
+-----+---------+---------+

How do you finish it with one statement?
Post's tid = thead's tid, and post max(pid)'s pid,uid is thead's value.

I want to get pid and uid from post max(pid) that post.tid eq thread.tid. And set the pid,uid to replace thread 's lastpid and lastuid.

According to the best answer, we rewrote it to solve this problem: UPDATE thread t INNER JOIN ( SELECT tid, uid AS last_uid, pid AS last_pid FROM post WHERE pid IN (SELECT max(pid) FROM post GROUP BY tid) ) p ON t.tid = p.tid SET t.lastuid = p.last_uid, t.lastpid = p.last_pid;

Upvotes: 0

Views: 50

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522732

You may try an update join:

UPDATE thread t
INNER JOIN
(
    SELECT tid, MAX(uid) AS max_uid, MAX(pid) AS max_pid
    FROM post
    GROUP BY tid
) p
    ON t.tid = p.tid
SET
    t.lastuid = p.max_pid,
    t.lastpid = p.max_uid;

If you instead just want to do a select, then the above can be slightly modified, basically just by replacing UPDATE... with SELECT.

Note that it appears that lastuid and lastpid have been swapped in your expected output.

Upvotes: 3

Related Questions