List:General Discussion« Previous MessageNext Message »
From:Dante Lorenso Date:December 22 2009 8:53pm
Subject:UPDATE and simultaneous SELECT ... similar to RETURNING?
View as plain text  
All,

There was a feature of another DB that I have grown extremely accustomed to
and would like to find the equivalent in MySQL:

UPDATE mytable SET
  mycolumn = mycolumn + 1
WHERE mykey = 'dante'
RETURNING mycolumn;

The magic of this statement is in the "RETURNING" clause.  RETURNING causes
every update statement to become a select statement also where the rows
affected by the update can also be returned.  This works for multiple rows
or just one and is how I have been able to do in 1 step what otherwise seems
to require many.

In MySQL, I have found this so far:

UPDATE mytable SET
  mycolumn = @mycolumn := mycolumn + 1
WHERE mykey = 'dante';

SELECT @mycolumn;

This provides the same solution as the query above, but it has to be
performed in 2 steps and it won't work for multiple rows since the @mycolumn
variable will be overwritten for each matched row in the WHERE clause.

Does anyone have suggestions on a MySQL pattern that might achieve what I'm
after?  Any word on whether the RETURNING syntax might be added to the
supported SQL syntax some time in the future?

Dante

-- 
D. Dante Lorenso

Thread
UPDATE and simultaneous SELECT ... similar to RETURNING?Dante Lorenso22 Dec
  • Re: UPDATE and simultaneous SELECT ... similar to RETURNING?Baron Schwartz25 Dec