Deleting multiple rows based on where clause using FluentMigrator

Question

Given a Payment table, a PaymentStatus table, and a PaymentHistory table, I need to delete a status from the PaymentStatus table that the Payment and PaymentHistory tables have associated records for. So naturally that means delete the history table before the payment table. Of course the historical data has multiple records of payments in different statuses. What I need is a where clause for the history table delete... the sql would look kind of like this:

DELETE FROM PaymentHistory 
WHERE PaymentId IN 
(SELECT Id FROM Payment WHERE PaymentStatusCode = 'Processing')

What I don't understand is how to achieve this in FluentMigrator. This is what I have so far but there are no .Where() or other extension methods available after the .FromTable()

Delete.FromTable("PaymentHistory").Row(new {PaymentStatusCode = "Processing"});

Thanks in advance for the help.

score 7 · Accepted Answer · edited Aug 04 '14 at 16:04

7

Now I feel stupid for asking....

const string cascadeDeleteScript = 
@"DELETE FROM PaymentHistory WHERE PaymentId IN 
              (SELECT Id FROM Payment WHERE PaymentStatusCode = 'Processing') ";
Execute.Sql(cascadeDeleteScript);

edited Aug 04 '14 at 16:04

Castrohenge

8,525
5
39
66

answered Jun 17 '14 at 18:37

MegaMark

600
8
26

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

How about setting the table up to Cascade Delete?

How to add 'ON DELETE CASCADE' in ALTER TABLE statement

It seems like you would have to remove the Primary-Foreign Key constraint and recreate it with the Cascade in mind, but then any time you delete from Payment you will also delete from PaymentHistory.

edited May 23 '17 at 12:03

Community

1
1

answered Oct 04 '16 at 14:43

user3654055

178
14

Deleting multiple rows based on where clause using FluentMigrator

2 Answers2