用其他列的值更新表列

I have a table along these lines:

我有这样一张桌子:

Client | Date       | Value 1 | Value 2 |
1       2013-11-08    159      159
1       2013-11-09    254      254
1       2013-12-05    512      512
1       2014-01-02    1200     1200
2       2013-11-10    189      189
2       2013-11-15    289      289
2       2013-12-22    585      585
2       2014-01-06    1650     1650

I need to update the table in SQL to look like this:

我需要更新SQL中的表，让它看起来像这样:

Client | Date       | Value 1 | Value 2 |
1       2013-11-08    159      1200
1       2013-11-09    254      1200
1       2013-12-05    512      1200
1       2014-01-02    1200     1200
2       2013-11-10    189      1650
2       2013-11-15    289      1650
2       2013-12-22    585      1650
2       2014-01-06    1650     1650

The idea is that for each Client, Value 2 will become Value 1 where Date is most recent.

其思想是，对于每个客户端，Value 2将变成Value 1，在日期最近的地方。

2 个解决方案

#1

In SQL Server the best best thing to use is CTE with UPDATE statement. The query below demonstrates the syntax for what you need to do. All you have to do is substitute your table name and columns names.

在SQL Server中最好使用的是CTE和UPDATE语句。下面的查询演示了需要做什么的语法。只需替换表名和列名。

;WITH    MyUpdate
          AS ( SELECT ClientId
                   ,Value1
                   ,ROW_NUMBER() OVER ( PARTITION BY ClientId ORDER BY MyDate DESC ) AS RowNum
                FROM MyTable)
    UPDATE MyTable
        SET MyTable.Value2 = MyUpdate.Value1
        FROM MyTable
        INNER JOIN MyUpdate
            ON MyUpdate.ClientID = MyTable.ClientID
               AND RowNum = 1

#2

Try this:

试试这个:

UPDATE TABLE1 T2 SET Value2 =
(SELECT T1.Value2 FROM TABLE1 T1 WHERE T1.Client = T2.Client AND
T1.Date = (SELECT MAX(T3.Date) FROM TABLE1 T3 
           WHERE T2.Client = T3.Client GROUP BY Client));

ORACLE