1

Following is my query

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

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

I have already refered :

How to UPDATE and SELECT at the same time in MySQL

Is there a way to SELECT and UPDATE rows at the same time?

etc.. but could not modify my query..

Sugumar Venkatesan
  • 4,019
  • 8
  • 46
  • 77
  • https://stackoverflow.com/questions/24691576/mysql-update-column-then-select-updated-value - though I wonder whether not a historical log is wanted or something (insert and get generated key). – Joop Eggen Jul 12 '17 at 08:18

1 Answers1

1

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.

For a single row:

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;

For multiple rows:

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;
fancyPants
  • 50,732
  • 33
  • 89
  • 96