0

myTable has columns Id, Name, Type, MediaName, Folder

blacklistTable has columns Id, Media

DELETE FROM myTable
WHERE Name = "Missing" AND Type = "Movie"

Basically, I want to delete all items matching the WHERE statement from myTable, and for each item deleted, I want to add a column to blacklist table that has the deleted items Id and MediaName. Is there some neat way to do this?

asdasd3
  • 11
  • If you using SQL Server, check out Magic Tables ... no joke ... just google SQL Server Magic Tables – Veljko89 Jul 20 '17 at 14:21
  • 1
    Why you want to add columns to a table according to deleted rows in another table? – Tim Schmelter Jul 20 '17 at 14:22
  • Possible duplicate of [INSERT deleted values into a table before DELETE with a DELETE TRIGGER](https://stackoverflow.com/questions/14018982/insert-deleted-values-into-a-table-before-delete-with-a-delete-trigger) – Woot4Moo Jul 20 '17 at 14:23
  • careful when using @Veljko89's suggestion, when creating a trigger to accomplish the functionality you've described, you won't want the trigger to process for the deletes initiated from within the trigger. – Nate Anderson Jul 20 '17 at 14:24
  • @TimSchmelter sounds like auditing. Possibly an XY problem here. Maybe OP needs to look at soft deletes or another approach. – Jacob H Jul 20 '17 at 14:27
  • You want to add a *column* or rather a *row* to your blacklist table? – Thorsten Kettner Jul 20 '17 at 14:37

1 Answers1

1

You could either just do a SELECT INTO before deleting your data.

Or you can use the OUTPUT clause along with the INTO clause, like

DELETE FROM MyTable
OUTPUT DELETED.Id, DELETED.MediaName INTO Blacklist
WHERE Name = "Missing" AND Type = "Movie"
Pierre-Alain Vigeant
  • 22,635
  • 8
  • 65
  • 101