UPDATE: This post is obsolete. Please check a more recent article on my professional website:
MariaDB RETURNING Statements
MariaDB 10.0.5 supports
DELETE ... RETURNING, like PostgreSQL. It deletes some rows and returns the specified columns (or, more generally, the specified
SELECT expressions) from the deleted rows. As for regular
DELETEs, Com_delete is incremented and Com_select is not.
Let’s see what this feature does and what it does not.
- Cannot use it with
CREATE TABLEto create a table containing data from the deleted rows (a sort of delete log, or easy-to-restore backup).
- Cannot use it as a subquery.
- Cannot use it as a cursor, and there is no
INTO. So you cannot process the results in a stored routine, because there is no way to copy deleted data into variables.
- Cannot use aggregating functions. But why should you? If the purpose is to get a row count, you can use
- Include a subquery, virtual column, stored function, or anything else in the
RETURNING *works, AS works.
- Use it as a prepared statement.
- Use it in a stored procedure. But in practice, you can only return a resultset.
Some of the things I included in this list should be obvious: I just stated that some documented features can work together. But I’m afraid this is not obvious in MySQL and MariaDB: they have great features, but in many cases they don’t work together.
UPDATE: Some of the limitations can be workarounded with this trick.
Thanks, MariaDB team!
DELETE RETURNING is another reason to use MariaDB. And I see a task for
UPDATE ... RETURNING.