0

I want to update a set of rows in a table with data from a different set of rows from the same table. Ex: I want to update Customer Invoice Amounts from year 2018 with Customer Invoice Amounts from Year 2017. In the below scenario I want to update Set2 with Amount and Product from Set1 for the respective Customer and Line Item.

Set 1

Customer, Year, Invoice Line, Amount, Product

Cus1, 2017, Line1, 200, PR1
Cus1, 2017, Line2, 300, PR2
Cus2, 2017, Line1, 500, PR2

Set2

Cus1, 2018, Line1, 0, 
Cus1, 2018, Line2, 0,
Cus2, 2018, Line1, 0,

Can I accomplish this using MERGE command. I am not planning to insert data I just want to update the Set2 with any available data in Set1.

2
  • 1
    What if there are missing/mismatching rows (eg 3 rows in 2018 but 1 in 2017)? What if there are the same number of rows but the details differ (Cus1, Cu2 in 2018 but Cu2, Cus3 in 2017)? Mar 8 '17 at 23:21
  • What if there are missing/mismatching rows (eg 3 rows in 2018 but 1 in 2017)? Then only one row in 2018 should be updated. Not planning to insert any new rows. What if there are the same number of rows but the details differ (Cus1, Cu2 in 2018 but Cu2, Cus3 in 2017)? Most of the customers are same. If the customers don't match it can ignore. No need to update data in 2018 or insert any new rows...
    – Joe
    Mar 8 '17 at 23:27
4

Assuming that all rows in 2018 have a matching one in 2017 (with same values in Customer and Invoice Line) or you want to update only the matching rows, then you only need an UPDATE, not MERGE:

UPDATE upd
SET Amount  = old.Amount, 
    Product = old.Product
FROM dbo.Invoices AS upd
    JOIN dbo.Invoices AS old
    ON  upd.Customer       = old.Customer
    AND upd.[Invoice Line] = old.[Invoice Line]
WHERE upd.Year = 2018
  AND old.Year = 2017 ;

You may want to add AND upd.Amount = 0 as a safeguard so not to update good data but only rows with 0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.