PostgreSQL
PostgreSQL’s `RETURNING` clause is an officially supported implementation. A single column, multiple columns, or the entire row may be returned.
Return the `id` column
UPDATE my_table SET my_col = 'My Value' WHERE id = 123 RETURNING id
Return `id` and `my_col` columns
UPDATE my_table SET my_col = 'My Value' WHERE id = 123 RETURNING id, my_col
Return all columns
UPDATE my_table SET my_col = 'My Value' WHERE id = 123 RETURNING *
Microsoft SQL Server
MSSQL Server’s `OUTPUT` is an officially supported implementation. A single column, multiple columns, or the entire row may be returned.
Return the `id` column
UPDATE TOP (1) my_table SET my_col = 'My Value' OUTPUT Inserted.id WHERE id = 123
Return `id` and `my_col` columns
UPDATE TOP (1) my_table SET my_col = 'My Value' OUTPUT Inserted.id, Inserted.my_col WHERE id = 123
Return all columns
UPDATE TOP (1) my_table SET my_col = 'My Value' OUTPUT Inserted.* WHERE id = 123
MySQL
MySQL does not support returning when performing an UPDATE. To get around this, you can make use of the LAST_INSERT_ID() function. Unfortunately, this limits us to only returning a single column, and that column must be a type of integer (tinyint, smallint, int or bigint, signed or unsigned).
For this to work, either your driver must support multiple queries such as PHP’s mysqli::multi_query(), or you must perform two queries to retrieve the updated id. One for updating, one for retrieving the updated value.
Update and return the id in a single query:
UPDATE my_table
SET
id = LAST_INSERT_ID(id),
my_col = 'My Value'
WHERE
id = 123;
SELECT LAST_INSERT_ID();
Update in one query, returning `id` in a subsequent query:
UPDATE my_table
SET
id = LAST_INSERT_ID(id),
my_col = 'My Value'
WHERE
id = 123
SELECT LAST_INSERT_ID()
If you’re using the mysql_query() function to perform updates in PHP, instead of performing the subsequent query, you can simply make use of mysql_insert_id(). Example:
<?php
mysql_query("UPDATE my_table SET id = LAST_INSERT_ID(id), my_col = 'My Value' WHERE id = 123");
$id = mysql_insert_id();
?>