在单个查询中同时选择和更新:更新位于不同的列上 [英] select and update at the same time in a single query: update is on a different column

问题描述

以下是我的查询

select c.*,u.fullname,u.userphoto from chat_messages
   c left join us_signup u on u.id=c.fromid 
   where c.fromid in(?,?) and c.toid in(?,?) and c.jobid=? 
   order by c.received_date asc

chat_messages表中有一个列，我想更新

there is a column in the chat_messages table called "readi" which I want to update

我已经提到过:

如何在在MySQL中同时

是否存在一次同时选择和更新行的方法?

等等.但是无法修改我的查询.

etc.. but could not modify my query..

推荐答案

您链接到的答案都是针对MS SQL Server的(在带有MySQL标签的问题中，请阅读注释).在MySQL中，没有OUTPUT子句或其他任何子句.我能想到的唯一方法是使用用户定义的变量.

The answers you linked to are both for MS SQL Server (In the MySQL tagged question read the comments). In MySQL there is no OUTPUT clause or whatever. The only way I can think of is using user-defined variables.

对于单行:

SET @v := NULL;
UPDATE your_table
SET column_name = 'whatever'
WHERE another_column = 'foo'
AND @v := column_you_want_to_store
LIMIT 1; 
SELECT @v;

对于多行:

SET @v := '';
UPDATE your_table
SET column_name = 'whatever'
WHERE another_column = 'foo'
AND @v := (SELECT CONCAT_WS(', ', column_you_want_to_store, @v)); 
SELECT @v;

这篇关于在单个查询中同时选择和更新:更新位于不同的列上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！