Trevor Wood Trevor Wood - 1 year ago 72
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?


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

Tom Tom
Answer Source

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

   INT id,
   VARCHAR name


    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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download