从日期匹配的select语句中更新具有多个值的表

I'm having a problem updating a table and im sure its pretty straight forward but im going round and round in circles here.

我在更新表格时遇到了一个问题，我确信它是非常直接的，但是我在这里绕来绕去。

Table 'table1' data I want to update is formatted as follows:

我要更新的表'table1'数据格式如下:

[Month]                    Figure
----------------------------------
2010-05-01 00:00:00.000 1.0000
2010-06-01 00:00:00.000 1.0000
2010-07-01 00:00:00.000 1.0000
2010-08-01 00:00:00.000 1.0000

Table 'data1' that contains the updated figures is formatted as follows:

包含更新数据的“data1”表的格式如下:

[Month]                    Figure
----------------------------------
2010-05-01 00:00:00.000 0.7212
2010-08-01 00:00:00.000 1.2351

The SQL I'm using and the error message is as follows.

我正在使用的SQL和错误消息如下。

UPDATE t1
SET t1.figure = (SELECT figure from data1)
FROM table1 t1 JOIN data1 d1
ON (t1.[month] = d1.[month])


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Would i need a while loop to go through each row?

我需要一个while循环来遍历每一行吗?

I wish the end result to be as follows:

我希望最终结果如下:

[Month]                    Figure
----------------------------------
2010-05-01 00:00:00.000 0.7212
2010-06-01 00:00:00.000 1.0000
2010-07-01 00:00:00.000 1.0000
2010-08-01 00:00:00.000 1.2351

Much appreciated.

感谢。

3 个解决方案

#1

You can use the UPDATE FROMsyntax for this.

您可以为此使用UPDATE from语法。

Have a look at the syntax here and here.

看看这里和这里的语法。

FROM (table_source)

从(table_source)

Specifies that a table, view, or derived table source is used to provide the criteria for the update operation

指定使用表、视图或派生表源来提供更新操作的标准

UPDATE  t1
SET     t1.figure = data1.figure
FROM    t1
        INNER JOIN data1 ON data1.month = t1.month

#2

UPDATE t1
SET t1.figure = data1.figure 
FROM table1 t1 JOIN data1 d1
ON (t1.[month] = d1.[month])

#3

SQL Server 2008:

MERGE INTO Table1
USING data1 AS D1
   ON Table1.my_Month = D1.my_Month
WHEN MATCHED 
   THEN UPDATE 
           SET Figure = D1.Figure;

Pre-SQL Server 2008:

2008年Pre-SQL服务器:

UPDATE Table1
   SET Figure = (
                 SELECT D1.Figure
                   FROM data1 AS D1
                  WHERE Table1.my_Month = D1.my_Month
                )
 WHERE EXISTS (
               SELECT *
                 FROM data1 AS D1
                WHERE Table1.my_Month = D1.my_Month
              );

Note the UPDATE..FROM syntax is proprietary and can yield unpredictable results when a target row matches more than one source row.

请注意更新. .FROM语法是私有的，当目标行匹配多个源行时，可以产生不可预知的结果。

#1