检查线程的最后发件人和更新状态

发布于 2025-02-01 21:48:02 字数 596 浏览 3 评论 0原文

我有两个表

消息

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

线程

id status
5.  close
10. Open

我要实现的目标是，如果最新的send_id！= 1（aka not admin）＆gt;我想更新线程状态要打开。

到目前为止，我做了什么，

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)

我做错了什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一杯敬自由 2025-02-08 21:48:03

使用first_value（）窗口函数为每个thread_id最后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;

请参阅 demo 。
。

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.

回复收藏 0 原文

太阳公公是暖光 2025-02-08 21:48:03

我希望这会起作用。

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);

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);

回复收藏 0 原文

~没有更多了~