From: Dante Lorenso (dantelorenso.com)
Date: Tue Dec 22 2009 - 14:53:35 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]