30 April 2012

MYSQL update and select at the same time (almost)

How

You have to update to a variable then select the value of the variable.

update `datatable` set `rate` := @a := (`rate` + 2);

select @a as `index`;

(Note you have to use the funky := operator, which means this is really definitely an assignment operation)

But why do it like this?

This allows you an atomic operation, updating a column, and returning the value at the same time, without using table locking. Apparently the update operation is atomic, so provided the database connection is not shared @a will not be overwritten.

No comments: