CODEWITHSUNDEEP
c#delete-rowfluent-migrator
MegaMark
MegaMark

Reputation: 610

Deleting multiple rows based on where clause using FluentMigrator

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.

Upvotes: 4

Views: 4217

Answers (2)

user3654055
user3654055

Reputation: 178

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.

Upvotes: 0

MegaMark
MegaMark

Reputation: 610

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);

Upvotes: 8

Related Questions