2

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

Trevor Wood
  • 2,347
  • 5
  • 31
  • 56

3 Answers3

3

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.

Community
  • 1
  • 1
Tom
  • 1,387
  • 3
  • 19
  • 30
1

SELECT id FROM table WHERE time <= -1 week

and then simply

DELETE FROM table WHERE time <= -1 week

Makaronodentro
  • 907
  • 1
  • 7
  • 21
  • Thanks for the info bud, but I was wondering if there was a way to do it in one line? – Trevor Wood Oct 16 '16 at 01:38
  • Sure, call a stored proc. One line. – Drew Oct 16 '16 at 01:43
  • I am not an expert in them but the only thing i can think of is to use a ["trigger"](http://dev.mysql.com/doc/refman/5.7/en/triggers.html) to store the info – Makaronodentro Oct 16 '16 at 01:45
  • Well triggers are for auto running on insert update delete. So if that is not the expected behavior then don't do that. You can however do an Event that fires on a schedule, like cron, but baked into mysql without cron. See [one](http://stackoverflow.com/a/37901661) and [two](http://stackoverflow.com/a/32508935) – Drew Oct 16 '16 at 01:48
0

I would not search non indexed column twice. You should use a variable like:

SELECT id INTO @tID FROM table WHERE time <= -1 week;

DELETE FROM table WHERE id = @tID

You may then use the variable @tID as you wish.

Thanasis
  • 329
  • 4
  • 8