arodrisa arodrisa - 1 year ago 70
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 Source

You need to use SELF JOIN

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) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download