CODEWITHSUNDEEP
mysqlsql
Trevor
Trevor

Reputation: 2457

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

Upvotes: 2

Views: 8003

Answers (3)

Thanasis
Thanasis

Reputation: 439

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.

Upvotes: 0

Tom
Tom

Reputation: 1411

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.

Upvotes: 3

Makaronodentro
Makaronodentro

Reputation: 957

SELECT id FROM table WHERE time <= -1 week

and then simply

DELETE FROM table WHERE time <= -1 week

Upvotes: 1

Related Questions