1

Using SQL Server 2008 R2: I have a table called Data. I want delete all rows in which activity A and activity B both occur in the same date range for the same account (as represented below by field "key"), as together they constitute a "wash".

If I code:

Delete Data D1
from Data1
inner join Data D2
on D1.key = D2.key
and D1.Activity = 'A'
and D2.Activity = 'B'
and D1.TranDate < EndDateRange
and D1.TranDate >= BeginDateRange
and D2.TranDate < EndDateRange
and D2.TranDate >= BeginDateRange

I can delete the row with activity 'A' from the table. How can I also delete activity 'B'? Can I do this in the same query? Once row A is deleted, the account no longer shows a wash condition.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DeveloperM
  • 1,129
  • 7
  • 17
  • 30
  • 1
    Either enforce a cascade delete between the tables on the structure itself. Or you'd have to have a procedure execute the individual deletes based on the data set achieved from your query. You'd want this in a transaction so that both succeed before you commit; otherwise you lose the ability to identify what was a duplicate. Or, if you have a spare field, you could update the field in both tables then delete from that table where that field flag has been set. Or you could create a procedure with a temp table holding results then delete from both based on whats in temp table... – xQbert Jul 22 '14 at 18:40
  • xQBert, thanks for responding. Can you elaborate? What is a cascade delete? – DeveloperM Jul 22 '14 at 18:43
  • See answer with links below. – xQbert Jul 22 '14 at 18:47
  • possible duplicate of [How do I use cascade delete with SQL Server?](http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server) – xQbert Jul 22 '14 at 18:57

1 Answers1

1

How can I also delete activity 'B'?

  • use cascade delete - Props to Marc_s who edited your question and gave you the answer in a previous post.
  • use a trigger
  • use a procedure and a temp table write results to temp table and then delete from each table based on what's in the temp table.
  • set a flag in both tables and delete based on the flags removing the needed association temporarily thus when you delete from one, you still have the one flagged you need to delete.
  • use a procedure and (shudder) cursor
  • lots of options here... just depends on extenuating circumstances. Order listed is order I'd try them given simple circumstances.

Can I do this in the same query?

Only if cascade delete is enabled between the two tables; or you have a trigger or something that can fire the second delete. You can't delete from two tables at once

Community
  • 1
  • 1
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Since there might also be activities 'C', 'D', etc. (which I do not want to delete), I am going with your suggestion to use a spare field to flag the wash entries. Thank you for the discussion of the possibilities. – DeveloperM Jul 22 '14 at 18:57
  • It's why extenuating circumstances matter. No one answer works in all cases. Context is important thus options need to be presented to cover context which may not have been communicated. the cascade delete seemed to be the best answer to me given what we knew: however, there could be more than what was presented, thus the other options. Good luck! (Think outside the box? What box?) – xQbert Jul 22 '14 at 19:00