4258  

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

647  

In SQL Server 2008 (or better), use MERGE

在SQL Server 2008(或更好)中，使用MERGE。

MERGE INTO YourTable T
   USING other_table S 
      ON T.id = S.id
         AND S.tsql = 'cool'
WHEN MATCHED THEN
   UPDATE 
      SET col1 = S.col1, 
          col2 = S.col2;

Alternatively:

另外:

MERGE INTO YourTable T
   USING (
          SELECT id, col1, col2 
            FROM other_table 
           WHERE tsql = 'cool'
         ) S
      ON T.id = S.id
WHEN MATCHED THEN
   UPDATE 
      SET col1 = S.col1, 
          col2 = S.col2;

503  

UPDATE table 
SET Col1 = i.Col1, 
    Col2 = i.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) i
WHERE 
    i.ID = table.ID

243  

I'd modify Robin's excellent answer to the following:

我将修改Robin对以下内容的出色回答:

UPDATE Table
SET Table.col1 = other_table.col1,
 Table.col2 = other_table.col2
FROM
    Table
INNER JOIN other_table ON Table.id = other_table.id
WHERE
    Table.col1 != other_table.col1
OR Table.col2 != other_table.col2
OR (
    other_table.col1 IS NOT NULL
    AND Table.col1 IS NULL
)
OR (
    other_table.col2 IS NOT NULL
    AND Table.col2 IS NULL
)

Without a WHERE clause, you'll affect even rows that don't need to be affected, which could (possibly) cause index recalculation or fire triggers that really shouldn't have been fired.

如果没有WHERE子句，您甚至会影响不需要受影响的行，这可能(可能)导致索引重新计算或触发不应该被触发的触发器。

170  

One way

的一种方法

UPDATE t 
SET t.col1 = o.col1, 
    t.col2 = o.col2
FROM 
    other_table o 
  JOIN 
    t ON t.id = o.id
WHERE 
    o.sql = 'cool'

129  

Another possibility not mentioned yet is to just chuck the SELECT statement itself into a CTE then Update the CTE.

另一种可能还没有提到，就是把SELECT语句本身放入CTE中，然后更新CTE。

;WITH CTE
     AS (SELECT T1.Col1,
                T2.Col1 AS _Col1,
                T1.Col2,
                T2.Col2 AS _Col2
         FROM   T1
                JOIN T2
                  ON T1.id = T2.id
         /*Where clause added to exclude rows that are the same in both tables
           Handles NULL values correctly*/
         WHERE EXISTS(SELECT T1.Col1,
                             T1.Col2
                       EXCEPT
                       SELECT T2.Col1,
                              T2.Col2))
UPDATE CTE
SET    Col1 = _Col1,
       Col2 = _Col2  

This has the benefit that it is easy to run the SELECT statement on its own first to sanity check the results but it does requires you to alias the columns as above if they are named the same in source and target tables.

这样做的好处是，可以很容易地运行SELECT语句来检查结果，但它确实需要您在源表和目标表中指定相同的列时，将其别名为上面。

This also has the same limitation as the proprietary UPDATE ... FROM syntax shown in four of the other answers. If the source table is on the many side of a one to many join then it is undeterministic which of the possible matching joined records will be used in the Update (An issue that MERGE avoids by raising an error if there is an attempt to update the same row more than once).

这也与专有更新有相同的限制…从其他四个答案中所显示的句法。如果源表在许多方面的一个许多加入然后undeterministic可能的匹配加入记录将用于更新(一个合并的问题,避免了通过提高一个错误如果有不止一次试图更新同一行)。

86  

For the record (and others searching like I was), you can do it in MySQL like this:

对于记录(以及其他类似的搜索)，您可以在MySQL中这样做:

UPDATE first_table, second_table
SET first_table.color = second_table.color
WHERE first_table.id = second_table.foreign_id

71  

Using alias:

使用别名:

UPDATE t
   SET t.col1 = o.col1
  FROM table1 AS t
         INNER JOIN 
       table2 AS o 
         ON t.id = o.id

53  

The simple way to do it is:

简单的方法是:

UPDATE
    table_to_update,
    table_info
SET
    table_to_update.col1 = table_info.col1,
    table_to_update.col2 = table_info.col2

WHERE
    table_to_update.ID = table_info.ID

43  

This may be a niche reason to perform an update (for example, mainly used in a procedure), or may be obvious to others, but it should also be stated that you can perform an update-select statement without using join (in case the tables you're updating between have no common field).

这可能是执行更新的特殊原因(例如，主要用于过程中)，或者可能对其他人来说是显而易见的，但是也应该声明，您可以在不使用连接的情况下执行update-select语句(如果您正在更新的表之间没有公共字段)。

update
    Table
set
    Table.example = a.value
from
    TableExample a
where
    Table.field = *key value* -- finds the row in Table 
    AND a.field = *key value* -- finds the row in TableExample a

41  

Here is another useful syntax:

下面是另一个有用的语法:

UPDATE suppliers
SET supplier_name = (SELECT customers.name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

It checks if it is null or not by using "WHERE EXIST".

它通过使用“WHERE EXIST”来检查是否为null。

39  

If you use MySQL instead of SQL Server, the syntax is:

如果使用MySQL而不是SQL Server，语法是:

UPDATE Table1
INNER JOIN Table2
ON Table1.id = Table2.id
SET Table1.col1 = Table2.col1,
    Table1.col2 = Table2.col2

38  

I add this only so you can see a quick way to write it so that you can check what will be updated before doing the update.

我只添加了这个，这样您就可以看到一个快速的方法来编写它，这样您就可以在更新之前检查将要更新的内容。

UPDATE Table 
SET  Table.col1 = other_table.col1,
     Table.col2 = other_table.col2 
--select Table.col1, other_table.col,Table.col2,other_table.col2, *   
FROM     Table 
INNER JOIN     other_table 
    ON     Table.id = other_table.id 

29  

UPDATE from SELECT with INNER JOIN in SQL Database

用SQL数据库中的内连接选择更新。

Since there are too many replies of this post, which are most heavily up-voted, I thought I would provide my suggestion here too. Although the question is very interesting, I have seen in many forum sites and made a solution using INNER JOIN with screenshots.

因为这篇文章的回复太多了，我认为我也会在这里提供我的建议。虽然这个问题很有趣，但我在很多论坛上都看到过，并且用内部连接和截屏做了一个解决方案。

At first, I have created a table named with schoolold and inserted few records with respect to their column names and execute it.

首先，我创建了一个名为schoolold的表，并在它们的列名中插入了很少的记录并执行它。

Then I executed SELECT command to view inserted records.

然后执行SELECT命令查看插入的记录。

Then I created a new table named with schoolnew and similarly executed above actions on it.

然后我创建了一个新表，命名为schoolnew，类似地执行上面的操作。

Then, to view inserted records in it, I execute SELECT command.

然后，为了查看插入的记录，我执行SELECT命令。

Now, Here I want to make some changes in third and fourth row, to complete this action, I execute UPDATE command with INNER JOIN.

现在，我想在第三和第四行做一些修改，来完成这个操作，我使用内部连接执行UPDATE命令。

To view the changes I execute the SELECT command.

要查看更改，我执行SELECT命令。

You can see how Third and Fourth records of table schoolold easily replaced with table schoolnew by using INNER JOIN with UPDATE statement.

您可以通过使用内部连接和UPDATE语句，来查看表schoolold的第三和第四记录如何轻松地替换为table schoolnew。

27  

And if you wanted to join the table with itself (which won't happen too often):

如果你想和自己一起吃饭(这种情况不会经常发生):

update t1                    -- just reference table alias here
set t1.somevalue = t2.somevalue
from table1 t1               -- these rows will be the targets
inner join table1 t2         -- these rows will be used as source
on ..................        -- the join clause is whatever suits you

27  

The following example uses a derived table, a SELECT statement after the FROM clause, to return the old and new values for further updates:

下面的示例使用派生表，在FROM子句后面的SELECT语句，以返回旧的和新的值，以便进一步更新:

UPDATE x
SET    x.col1 = x.newCol1,
       x.col2 = x.newCol2
FROM   (SELECT t.col1,
               t2.col1 AS newCol1,
               t.col2,
               t2.col2 AS newCol2
        FROM   [table] t
               JOIN other_table t2
                 ON t.ID = t2.ID) x

24  

Updating through CTE is more readable than the other answer's here:

通过CTE的更新比其他答案更容易阅读:

;WITH cte
     AS (SELECT col1,col2,id
         FROM   other_table
         WHERE  sql = 'cool')
UPDATE A
SET    A.col1 = B.col1,
       A.col2 = B.col2
FROM   table A
       INNER JOIN cte B
               ON A.id = B.id

21  

If you are using SQL Server you can update one table from another without specifying a join and simply link the two from the where clause. This makes a much simpler SQL Query:

如果您使用的是SQL Server，那么您可以从另一个表中更新一个表，而不需要指定一个连接，并简单地将二者链接到where子句中。这就产生了一个简单得多的SQL查询:

UPDATE Table1
SET Table1.col1 = Table2.col1,
 Table1.col2 = Table2.col2
FROM
    Table2
WHERE
    Table1.id = Table2.id

14  

The other way is to use a derived table:

另一种方法是使用派生表:

UPDATE t
SET t.col1 = a.col1
    ,t.col2 = a.col2
FROM (
SELECT id, col1, col2 FROM @tbl2) a
INNER JOIN @tbl1 t ON t.id = a.id

Sample data

样本数据

DECLARE @tbl1 TABLE (id INT, col1 VARCHAR(10), col2 VARCHAR(10))
DECLARE @tbl2 TABLE (id INT, col1 VARCHAR(10), col2 VARCHAR(10))

INSERT @tbl1 SELECT 1, 'a', 'b' UNION SELECT 2, 'b', 'c'

INSERT @tbl2 SELECT 1, '1', '2' UNION SELECT 2, '3', '4'

UPDATE t
SET t.col1 = a.col1
    ,t.col2 = a.col2
FROM (
SELECT id, col1, col2 FROM @tbl2) a
INNER JOIN @tbl1 t ON t.id = a.id

SELECT * FROM @tbl1
SELECT * FROM @tbl2

13  

UPDATE TQ
SET TQ.IsProcessed = 1, TQ.TextName = 'bla bla bla'
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0

To make sure you are updating what you want, select first

为了确保您正在更新您想要的，首先选择。

SELECT TQ.IsProcessed, 1 AS NewValue1, TQ.TextName, 'bla bla bla' AS NewValue2
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0

11  

drop table uno
drop table dos

create table uno
(
uid int,
col1 char(1),
col2 char(2)
)
create table dos
(
did int,
col1 char(1),
col2 char(2),
[sql] char(4)
)
insert into uno(uid) values (1)
insert into uno(uid) values (2)
insert into dos values (1,'a','b',null)
insert into dos values (2,'c','d','cool')

select * from uno 
select * from dos

EITHER:

:

update uno set col1 = (select col1 from dos where uid = did and [sql]='cool'), 
col2 = (select col2 from dos where uid = did and [sql]='cool')

OR:

或者:

update uno set col1=d.col1,col2=d.col2 from uno 
inner join dos d on uid=did where [sql]='cool'

select * from uno 
select * from dos

If the ID column name is the same in both tables then just put the table name before the table to be updated and use an alias for the selected table ie:

如果两个表中的ID列名称相同，那么只需将表名放在表中，以便更新，并使用选定表的别名:

update uno set col1 = (select col1 from dos d where uno.[id] = d.[id] and [sql]='cool'),
col2  = (select col2 from dos d where uno.[id] = d.[id] and [sql]='cool')

11  

There is even a shorter method and might be surprising for many of you:

甚至还有更短的方法，可能会让很多人感到惊讶:

-- Sample data:
---------------------------------------------------------------------------
CREATE TABLE #SOURCE ([ID] INT, [Desc] VARCHAR(10));
CREATE TABLE #DESTINATION ([ID] INT, [Desc] VARCHAR(10))

INSERT INTO #SOURCE VALUES(1,'Desc_1'), (2, 'Desc_2'), (3, 'Desc_3');
INSERT INTO #DESTINATION VALUES(1,'Desc_4'), (2, 'Desc_5'), (3, 'Desc_6');
---------------------------------------------------------------------------
UPDATE #DESTINATION
SET #DESTINATION.[Desc] = #SOURCE.[Desc]
FROM #SOURCE
WHERE #DESTINATION.[ID] = #SOURCE.[ID]
AND #Source.[Desc] = 'Desc_2'

10  

I finally got this simple solution:

我终于得到了这个简单的解:

UPDATE table1 a , table2 b 
SET a.columname = 'some value' 
WHERE b.columnname IS NULL ;

10  

The other way to update from select statement :

UPDATE A
SET A.col = A.col,B.col1 = B.col1
FROM  first_Table AS A
INNER JOIN second_Table AS B  ON A.id = B.id WHERE A.col2 = 'cool'

10  

In the accepted answer, after the:

在接受的答案中，在:

SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2

I would add:

我想添加:

OUTPUT deleted.*, inserted.*

What I usually do is putting everything in a rollbacked transaction and using the "OUTPUT": in this way I see everything that is about to happen. When I am happy with what I see, i change the ROLLBACK into COMMIT.

我通常做的是把所有东西都放到一个滚动的事务中，并使用“输出”:这样我就能看到即将发生的一切。当我对我所看到的感到高兴时，我就把回滚改为提交。

I usually need to document what I did, so I use the "results to Text" option when I run the rollbacked query and I save both the script and the result of the OUTPUT. (Of course this is not practical if I changed too many rows)

我通常需要记录我所做的事情，所以当我运行rollback查询时，我使用“results to Text”选项，同时保存脚本和输出结果。(当然，如果我改变了太多的行，这是不实际的)

8  

UPDATE table AS a
INNER JOIN table2 AS b
ON a.col1 = b.col1
INNER JOIN ... AS ...
ON ... = ...
SET ...
WHERE ...

1  

Consolidating all the different Approaches here..

整合所有不同的方法。

1. Select Update
2. 选择更新
3. Update with Common Table Expression
4. 使用公共表表达式进行更新。
5. Merge
6. 合并

Sample Table Structure:

示例表结构:

Product

产品

    CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Description] [nvarchar](100) NULL
 ) ON [PRIMARY]

Product_BAK

Product_BAK

    CREATE TABLE [dbo].[Product_BAK](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [Description] [nvarchar](100) NULL
     ) ON [PRIMARY]

1. Select Update

1。选择更新

    update P1
    set Name=P2.Name
    from Product P1
    inner join Product_Bak P2 on p1.id=P2.id
    where p1.id=2

2. Update with Common Table Expression

2。使用公共表表达式进行更新。

    ;with cte as 
    (
    select id,name from Product_Bak where id=2
    )
    update P
    set Name=P2.name
    from  product P  inner join CTE P2 on P.id=P2.id
    where P2.id=2

3.Merge

3.合并

    Merge into product P1
    using Product_Bak P2 on P1.id=P2.id

    when matched then 
    update set p1.[description]=p2.[description],p1.name=P2.Name;

In Merge statement , we can do inset if not find matching record in Target but exist in Source and please find he syntax.

在Merge语句中，如果没有找到目标中的匹配记录，但在源中存在，请找到他的语法，我们可以做inset。

    Merge into product P1
    using Product_Bak P2 on P1.id=P2.id

    when matched then 
    update set p1.[description]=p2.[description],p1.name=P2.Name

    WHEN NOT MATCHED THEN  
    insert (name,description)
    values(p2.name,P2.description);