mysql update column value from another table

Question

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;

I think you switched the data in the lastpid and lastuid columns. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 12, 2018 at 6:35
My english is poor. But the problem has been solved. Thank you. — skiy
– skiy, Commented Apr 12, 2018 at 6:45

Tim Biegeleisen · Accepted Answer · 2018-04-12 07:12:55Z

3

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.

edited Apr 12, 2018 at 7:12

answered Apr 12, 2018 at 6:34

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

skiy Over a year ago

Thanks very much.

skiy Over a year ago

Sorry. There seems to be something wrong. I need lastpid's uid and lastpid's pid.

Tim Biegeleisen Over a year ago

@skiy Just swap the assignments in the SET clause then, q.v. my updated answer. I guess it wasn't a typo.

Collectives™ on Stack Overflow

mysql update column value from another table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related