arodrisa arodrisa - 1 month ago 9
SQL Question

Update values from table in same table SQL

I have a table in SQL which I want to update

NAME DATE Tenor Value
Item1 2016/01/01 1 0.1
Item1 2016/01/01 2 0.15
Item1 2016/01/01 3 0.16
Item1 2016/01/02 1 0.17
Item1 2016/01/02 2 0.18
Item1 2016/01/02 3 0.19
Item2 2016/01/01 1 0.11


I want to update values for "Item1" on "2016/01/02", with the values of "2016/01/01" for each tenor. For example.


For 2016/01/02 and Tenor 1, update with the value of 2016/01/01 and Tenor 1.

For 2016/01/02 and Tenor 2, update with the value of 2016/01/01 and Tenor 2.


Is there an easy way so that I can change my table without hardcoding the tenor? I have a huge table with many options. But the Tenors are the same for the dates, and I will hardcode the dates and the names.

Thanks in advance

Answer

You need to use SELF JOIN

UPDATE a
SET    a.Value = b.Value
FROM   yourtable a
       JOIN yourtable b
         ON a.NAME = b.NAME
            AND a.Tenor = b.Tenor
            AND a.DATE = Dateadd(dd, 1, b.DATE) 
Comments