Is there other way to do this using transaction besides the one I have below? I

Question

Is there other way to do this using transaction besides the one I have below? I need to write a set of action queries coded as a transaction to reflect the following change: United Parcel Service has been purchased by Federal Express Corporation and the new company is named FedUP. Rename one of the vendors and delete the other after updating the VendorID column in the Invoices table. BEGIN TRAN UPDATE Invoices SET VendorID = 124 WHERE VendorID = 123 DELETE Vendors WHERE VendorID = 123 UPDATE Vendors SET VendorName = 'FedUP' WHERE VendorID = 124 COMMIT TRAN

Explanation / Answer

Solution 1. BEGIN TRAN UPDATE Invoices SET VendorID = 123 WHERE VendorID = 122 DELETE Vendors WHERE VendorID = 122 UPDATE Vendors SET VendorName = 'FedUP' WHERE VendorID = 123 COMMIT TRAN 2. BEGIN TRAN INSERT InvoiceArchive SELECT Invoices.* FROM Invoices LEFT JOIN InvoiceArchive ON Invoices.InvoiceID = InvoiceArchive.InvoiceID WHERE Invoices.InvoiceTotal - Invoices.CreditTotal - Invoices.PaymentTotal = 0 AND InvoiceArchive.InvoiceID IS NULL DELETE Invoices WHERE InvoiceID IN (SELECT InvoiceID FROM InvoiceArchive) COMMIT TRAN or Below script would help you here.... Just check for Logical Errors, as I have used my own notations and values. BEGIN TRANSACTION /* Primary Table*/ UPDATE VendorsTable SET VendorName='FedUP' WHERE VendorName='United Parcel Service' /* If Vendor_Id's of "United Parcel Service" & "Federal Express Corporation" are 100 & 110 respectively then, we ought to merge all the records of InvoicesTable to a single Vendor_Id and I chose it to be 100 . */ /* Secondary Table*/ UPDATE InvoicesTable SET vendor_id = 100 WHERE vendor_id IN ( 100,110) DELETE VendorsTable WHERE VendorName='Federal Express Corporation' COMMIT TRANSACTION

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.