MariaDB 10.0.5 supports
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
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
UPDATE ... RETURNING.