更新同一表SQL中的表的值

I have a table in SQL which I want to update

我有一个SQL表，我想更新

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.

我想更新“2016/01/02”上“Item1”的值，每个男高音的值为“2016/01/01”。例如。

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

对于2016/01/02和Tenor 1，请更新2016/01/01和Tenor 1的值。

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

对于2016/01/02和Tenor 2，请更新2016/01/01和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

提前致谢

2 个解决方案

#1

You need to use SELF JOIN

你需要使用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)

#2

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;

#1