Trevor Wood Trevor Wood - 1 month ago 7
MySQL Question

delete rows and return an id of the deleted rows

is it possible to delete a row and return a value of the deleted row?

example

DELETE FROM table where time <= -1 week
SELECT all id that were deleted

Tom Tom
Answer

If you want to control the operations in the database, you could consider to use JOURNAL tables. There's a question here in SO about this.

They are a mirrored table, usually populated by a trigger with the operation performed (update, delete). It stores the "old values" (the current values you can always get from the main table).

If implemented so, you could then SELECT from the journal table and have exactly what you needed.

Trying to give you an example:

Table USER

CREATE TABLE USER (
   INT id,
   VARCHAR name
)

Table USER_JN

CREATE TABLE USER_JN (
    INT id,
    VARCHAR name,
    VARCHAR operation
)

Then, for every operation you can populate the USER_JN and have a history of all changes (you should not have constraints in it).

If you delete, your operation column would have the delete value and you could use your select to check that.

It's not exactly "selecting the deleted row", but a way to make it possible.

Hope it's somehow useful.