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 DELETE
s, Com_delete is incremented and Com_select is not.
Let’s see what this feature does and what it does not.
You cannot:
- Cannot use it with
INSERT
orCREATE TABLE
to 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
SELECT ROW_COUNT()
.
You can:
- Include a subquery, virtual column, stored function, or anything else in the
RETURNING
clause. 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
.
Enjoy!