UPDATE and simultaneous SELECT ... similar to RETURNING?
am 22.12.2009 21:53:35 von Dante Lorenso--0016e6d99daa490afb047b5766b0
Content-Type: text/plain; charset=UTF-8
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
--0016e6d99daa490afb047b5766b0--