当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - 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

网友答案:

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) 
网友答案:

Using a sub-select has the advantage, that you'd get an error, if there was more than one value returned. With a Joined Update this might lead to unexpected results...

CREATE TABLE tbl (NAME VARCHAR(100),[DATE] DATE,Tenor INT,Value DECIMAL(4,2));
INSERT INTO tbl VALUES
 ('Item1',{d'2016-01-01'},1,0.1)
,('Item1',{d'2016-01-01'},2,0.15)
,('Item1',{d'2016-01-01'},3,0.16)
,('Item1',{d'2016-01-02'},1,0.17)
,('Item1',{d'2016-01-02'},2,0.18)
,('Item1',{d'2016-01-02'},3,0.19)
,('Item2',{d'2016-01-01'},1,0.11);

SELECT * FROM tbl;

UPDATE tbl SET Value=(SELECT tbl2.Value 
                       FROM tbl AS tbl2
                       WHERE tbl2.NAME=tbl.NAME 
                         AND tbl2.Tenor=tbl.Tenor
                         AND tbl2.[DATE]={d'2016-01-01'})
WHERE [DATE]={d'2016-01-02'};

SELECT * FROM tbl;
分享给朋友:
您可能感兴趣的文章:
随机阅读: