Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
MariaDB: DELETE … RETURNING
+4 Vote Up -1 Vote Down

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!


Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.