MariaDB: DELETE … RETURNING

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.

You cannot:

  • Cannot use it with INSERT or CREATE 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!

About these ads

4 thoughts on “MariaDB: DELETE … RETURNING

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s