0

I have a table that holds data about exported bills. Each bill has a vendor. There is a separate table which has items from that bill.

So there are multiple bills for a vendor and then again multiple line items per bill.

I'm not sure how to delete a vendor from the database. I need to loop through the bill table. Then for each bill that has that vendor I need to use that bill ID to delete all line items with that bill ID from the line items table.

I just don't know how I loop through the bill table and act on each bill id. If anyone could offer an explanation on how i do it that would be great. Cheers

I'm using SQL Server 2008

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
discodowney
  • 1,475
  • 6
  • 28
  • 58
  • You should provide a db schema and sample data. please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) And [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Nov 26 '15 at 21:27

1 Answers1

2

You can update your tables to include DELETE CASCADE

How do I use cascade delete with SQL Server?

Or delete in sequence

I understand Vendors has Bills and Bills has Items.

DELETE I
FROM Items I 
JOIN Bills B
  ON B.bill_id = I.bill_id    
WHERE B.vendor_id = @VendorID; 

DELETE B
FROM Bills B
WHERE B.vendor_id = @VendorID; 

DELETE V
FROM Vendor V
WHERE V.vendor_id = @VendorID;
Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Cheers man thanks. I never heard of the Cascade option before. As for the question, I asked it that way as I didnt want an exact answer, I wanted just a general description and then go work it out. I know that sounds awkward but i think its better to learn that way. Cheers again for the help – discodowney Nov 26 '15 at 22:20