How to return an entire row or part of it after UPDATE, INSERT .

Here is my query.

 UPDATE news SET title = 'changed' WHERE ID = 8 RETURNING *

It returns syntax error.

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax to use near 'RETURNING *' at line 1

I want to get the affected row data.

CodePudding user response：

MariaDB and MySQL in general does not have support for a RETURNING clause after an UPDATE statement.

You have to return the updated row in a separate statement directly after the UPDATE statement like this:

UPDATE news SET title = 'changed' WHERE ID = 8;
SELECT * FROM news WHERE ID = 8;

Or using the LAST_INSERT_ID(), if you're doing an INSERT, which is based on your connection (though, RETURNING is allowed for INSERT in MariaDB - see below).

INSERT INTO news (ID, title) VALUES (8, 'aaa');    
SELECT LAST_INSERT_ID();

Read more about it here from a previous question: return the updated value after running the update query in mysql

It does, however, support RETURNING rows after an INSERT or REPLACE statement (if you're using version 10.5 and higher), like this:

INSERT INTO news (ID, title) values (8, 'aaa') RETURNING *;

REPLACE INTO news VALUES (8,'changed') RETURNING *;

Fiddle here.

Read more about it in the MariaDB Documentation: REPLACE...RETURNING.