Checking last sender of a thread and update status-CodePudding

I have two tables

message

id ,thread_id ,sender_id ,message_content
1.   10.         2.          message_content_1
2.    5.          1          message_content_2

thread

id status
5.  close
10. Open

What I am trying to achieve is if the latest send_id!=1(aka not admin) > I want to update the thread status to open .

What I did so far

UPDATE thread
SET thread.status=open
RIGHT JOIN message
ON thread.id=message.thread_id 
WHERE message.senderId!=1 AND (SELECT max(message.id) FROM message WHERE message.thread_id=thread.id)

What did I do wrong?

CodePudding user response：

Use FIRST_VALUE() window function to get for each thread_id the last sender_id:

UPDATE thread t
INNER JOIN (
  SELECT DISTINCT thread_id, 
         FIRST_VALUE(sender_id) OVER (PARTITION BY thread_id ORDER BY id DESC) sender_id
  FROM message
) m ON m.thread_id = t.id
SET t.status = 'open'
WHERE m.sender_id <> 1;

See the demo.

CodePudding user response：

i hope this would work.

update thread join message on thread.id=message.thread_id set thread.status='open' where message.sender_id!=1 and (SELECT max(message.id) FROM message);