blocks|key|53283|text|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'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|53284|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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'
</code></pre>

blocks|key|52924|text|在SQL+Server2008(或更高版本)中，使用MERGE|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|52925|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;|code-block|syntax|javascript|52926|或者：|52927|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;|52928|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/bb510625.aspx^0|Q|5|Q|5|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

In SQL Server 2008 (or newer), use <a href="http://technet.microsoft.com/en-us/library/bb510625.aspx" rel="noreferrer"><code>MERGE</code></a>
<pre><code>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;
</code></pre>
Alternatively:
<pre><code>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;
</code></pre>

blocks|key|2538422|text|UPDATE+YourTable+
SET+Col1+=+OtherTable.Col1,+
++++Col2+=+OtherTable.Col2+
FROM+(
++++SELECT+ID,+Col1,+Col2+
++++FROM+other_table)+AS+OtherTable
WHERE+
++++OtherTable.ID+=+YourTable.ID|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2538423|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre class="lang-sql prettyprint-override"><code>UPDATE YourTable 
SET Col1 = OtherTable.Col1, 
 Col2 = OtherTable.Col2 
FROM (
 SELECT ID, Col1, Col2 
 FROM other_table) AS OtherTable
WHERE 
 OtherTable.ID = YourTable.ID
</code></pre>

blocks|key|2748374|text|我会将Robin's+excellent+answer修改为以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2748375|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
)|code-block|syntax|javascript|2748376|如果没有WHERE子句，您甚至会影响不需要受影响的行，这可能(可能)导致索引重新计算或触发实际上不应该触发的触发器。|2748377|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server/2334741#2334741^0|3|O|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

I'd modify <a href="https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server/2334741#2334741">Robin's excellent answer</a> to the following:

<pre><code>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
)
</code></pre>

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.

blocks|key|2748309|text|单行道|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2748310|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'|code-block|syntax|javascript|2748311|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

One way

<pre><code>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'
</code></pre>

blocks|key|52937|text|另一种尚未提及的可能性是将SELECT语句本身放入CTE中，然后更新CTE。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|52938|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;|code-block|syntax|javascript|52939|这样做的好处是，首先可以很容易地单独运行SELECT语句来检查结果，但是如果这些列在源表和目标表中的名称相同，则需要使用上面的别名。|52940|这也具有与其他四个答案中所示的专有UPDATE+...+FROM语法相同的限制。如果源表在一对多连接的多个端，那么不确定在Update中将使用哪些可能匹配的连接记录(如果多次尝试更新同一行，MERGE会通过引发错误来避免这个问题)。|52941|entityMap^0|D|6|0|0|K|6|0|H|F|1P|6|2N|5|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|P|$]]

Another possibility not mentioned yet is to just chuck the <code>SELECT</code> statement itself into a CTE and then update the CTE.
<pre><code>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;
</code></pre>
This has the benefit that it is easy to run the <code>SELECT</code> 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.
This also has the same limitation as the proprietary <code>UPDATE ... FROM</code> 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 <code>Update</code> (an issue that <code>MERGE</code> avoids by raising an error if there is an attempt to update the same row more than once).

blocks|key|2748501|text|对于记录(以及其他像我一样的搜索)，您可以在MySQL中这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2748502|UPDATE+first_table,+second_table
SET+first_table.color+=+second_table.color
WHERE+first_table.id+=+second_table.foreign_id|code-block|syntax|javascript|2748503|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>UPDATE first_table, second_table
SET first_table.color = second_table.color
WHERE first_table.id = second_table.foreign_id
</code></pre>

blocks|key|54438|text|使用别名：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|54439|UPDATE+t
+++SET+t.col1+=+o.col1
++FROM+table1+AS+t
+++++++++INNER+JOIN+
+++++++table2+AS+o+
+++++++++ON+t.id+=+o.id|code-block|syntax|javascript|54440|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Using alias:

<pre><code>UPDATE t
 SET t.col1 = o.col1
 FROM table1 AS t
 INNER JOIN 
 table2 AS o 
 ON t.id = o.id
</code></pre>

blocks|key|2538518|text|简单的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2538519|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|code-block|syntax|javascript|2538520|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The simple way to do it is:

<pre><code>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
</code></pre>

blocks|key|2538444|text|这可能是执行update的一个合适的原因(例如，主要用于过程中)，或者对其他人来说可能是显而易见的，但还应该说明的是，您可以在不使用join的情况下执行update-select语句(以防您正在更新的表之间没有公共字段)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2538445|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|code-block|syntax|javascript|2538446|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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).

<pre><code>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
</code></pre>

blocks|key|52965|text|下面是另一个有用的语法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|52966|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);|code-block|syntax|javascript|52967|它通过使用"WHERE+EXIST“检查它是否为null。|52968|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is another useful syntax:

<pre><code>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);
</code></pre>

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

blocks|key|53412|text|我添加这个只是为了让你看到一种快速编写它的方法，这样你就可以在进行更新之前检查要更新的内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53413|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+|code-block|syntax|javascript|53414|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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 
</code></pre>

blocks|key|54512|text|如果使用MySQL而不是SQL+Server，则语法为：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|54513|UPDATE+Table1
INNER+JOIN+Table2
ON+Table1.id+=+Table2.id
SET+Table1.col1+=+Table2.col1,
++++Table1.col2+=+Table2.col2|code-block|syntax|javascript|54514|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/MySQL^0|4|5|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

If you use <a href="http://en.wikipedia.org/wiki/MySQL">MySQL</a> instead of SQL Server, the syntax is:

<pre class="lang-sql prettyprint-override"><code>UPDATE Table1
INNER JOIN Table2
ON Table1.id = Table2.id
SET Table1.col1 = Table2.col1,
 Table1.col2 = Table2.col2
</code></pre>

blocks|key|2748897|text|数据库中的SQL+UPDATE+from+SELECT+with+INNER+JOIN|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2748898|由于这篇文章有太多的回复，大多数都是投票通过的，我想我也应该在这里提供我的建议。虽然这个问题非常有趣，但我已经在许多论坛网站上看到了这个问题，并使用带有屏幕截图的内部加入做了一个解决方案。|2748899|首先，我创建了一个名为的表，并插入了一些关于它们的列名的记录并执行它。|2748900|然后，我执行SELECT命令来查看插入的记录。|2748901|​|2748902|📷|atomic|2748903|2748904|然后，我创建了一个名为的新表，并在上面类似地执行了上面的操作。|2748905|2748906|2748907|2748908|然后，为了查看其中插入的记录，我执行SELECT命令。|2748909|2748910|2748911|2748912|现在，我想对第三行和第四行进行一些更改，为了完成此操作，我使用INNER+JOIN执行UPDATE命令。|2748913|2748914|2748915|2748916|要查看更改，我执行SELECT命令。|2748917|2748918|2748919|2748920|您可以看到，通过使用INNER+JOIN++UPDATE语句，表的第三条和第四条记录如何轻松地替换为表学校新。|2748921|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/f90ee66e672ff275f95ed967aa18d8b6.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/a39331c69a81428ca688b0229aee0b84.png|2|https://ask.qcloudimg.com/http-save/yehe-900000/03376c53c96d406dc8b95c72f80d4275.png|3|https://ask.qcloudimg.com/http-save/yehe-900000/1effb954f994a862ed12caa1db9713b0.png|4|https://ask.qcloudimg.com/http-save/yehe-900000/bdeb3e42413c2121142a4ab03af658f2.png^0|0|3|0|29|4|0|0|6|6|0|0|0|1|0|0|0|B|K|0|0|1|0|0|1|0|1|1|0|0|1|0|0|R|0|0|1|0|0|1|0|1|2|0|0|1|0|0|1G|0|0|1|0|0|1|0|1|3|0|0|1|0|0|I|0|0|1|0|0|1|0|1|4|0|0|1|0|0|1J|0^^$0|@$1|2|3|4|5|6|7|1U|8|@$9|1V|A|1W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1X|8|@$9|1Y|A|1Z|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|20|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|21|8|@$9|22|A|23|B|C]]|D|@]|E|$]]|$1|L|3|M|5|6|7|24|8|@]|D|@]|E|$]]|$1|N|3|O|5|P|7|25|8|@]|D|@$9|26|A|27|1|28]]|E|$]]|$1|Q|3|M|5|6|7|29|8|@]|D|@]|E|$]]|$1|R|3|S|5|6|7|2A|8|@$9|2B|A|2C|B|C]]|D|@]|E|$]]|$1|T|3|M|5|6|7|2D|8|@$9|2E|A|2F|B|C]]|D|@]|E|$]]|$1|U|3|O|5|P|7|2G|8|@$9|2H|A|2I|B|C]]|D|@$9|2J|A|2K|1|2L]]|E|$]]|$1|V|3|M|5|6|7|2M|8|@$9|2N|A|2O|B|C]]|D|@]|E|$]]|$1|W|3|X|5|6|7|2P|8|@$9|2Q|A|2R|B|C]]|D|@]|E|$]]|$1|Y|3|M|5|6|7|2S|8|@$9|2T|A|2U|B|C]]|D|@]|E|$]]|$1|Z|3|O|5|P|7|2V|8|@$9|2W|A|2X|B|C]]|D|@$9|2Y|A|2Z|1|30]]|E|$]]|$1|10|3|M|5|6|7|31|8|@$9|32|A|33|B|C]]|D|@]|E|$]]|$1|11|3|12|5|6|7|34|8|@$9|35|A|36|B|C]]|D|@]|E|$]]|$1|13|3|M|5|6|7|37|8|@$9|38|A|39|B|C]]|D|@]|E|$]]|$1|14|3|O|5|P|7|3A|8|@$9|3B|A|3C|B|C]]|D|@$9|3D|A|3E|1|3F]]|E|$]]|$1|15|3|M|5|6|7|3G|8|@$9|3H|A|3I|B|C]]|D|@]|E|$]]|$1|16|3|17|5|6|7|3J|8|@$9|3K|A|3L|B|C]]|D|@]|E|$]]|$1|18|3|M|5|6|7|3M|8|@$9|3N|A|3O|B|C]]|D|@]|E|$]]|$1|19|3|O|5|P|7|3P|8|@$9|3Q|A|3R|B|C]]|D|@$9|3S|A|3T|1|3U]]|E|$]]|$1|1A|3|M|5|6|7|3V|8|@$9|3W|A|3X|B|C]]|D|@]|E|$]]|$1|1B|3|1C|5|6|7|3Y|8|@$9|3Z|A|40|B|C]]|D|@]|E|$]]|$1|1D|3|-4|5|6|7|41|8|@]|D|@]|E|$]]]|1E|$1F|$5|1G|1H|1I|E|$1J|1K|1L|-4]]|1M|$5|1G|1H|1I|E|$1J|1N|1L|-4]]|1O|$5|1G|1H|1I|E|$1J|1P|1L|-4]]|1Q|$5|1G|1H|1I|E|$1J|1R|1L|-4]]|1S|$5|1G|1H|1I|E|$1J|1T|1L|-4]]]]

UPDATE from SELECT with INNER JOIN in SQL Database

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.

Then I executed SELECT command to view inserted records.

<a href="https://i.stack.imgur.com/OdtDP.png" rel="noreferrer"><img src="https://i.stack.imgur.com/OdtDP.png" alt=""></a>

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

<a href="https://i.stack.imgur.com/QP7Fw.png" rel="noreferrer"><img src="https://i.stack.imgur.com/QP7Fw.png" alt=""></a>

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

<a href="https://i.stack.imgur.com/BfqC5.png" rel="noreferrer"><img src="https://i.stack.imgur.com/BfqC5.png" alt=""></a>

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

<a href="https://i.stack.imgur.com/agP7W.png" rel="noreferrer"><img src="https://i.stack.imgur.com/agP7W.png" alt=""></a>

To view the changes I execute the SELECT command.

<a href="https://i.stack.imgur.com/ws0Xv.png" rel="noreferrer"><img src="https://i.stack.imgur.com/ws0Xv.png" alt=""></a>

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

blocks|key|53464|text|如果你想把表和它自己连接起来(这种情况不会经常发生)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53465|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|code-block|syntax|javascript|53466|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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
</code></pre>

blocks|key|2538873|text|通过CTE更新比这里的其他答案更具可读性：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2538874|;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|code-block|syntax|javascript|2538875|entityMap^0|2|3|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Updating through <code>CTE</code> is more readable than the other answers here:

<pre><code>;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
</code></pre>

blocks|key|2538537|text|下面的示例使用派生表(+FROM子句后的SELECT语句)返回旧值和新值以进行进一步更新：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2538538|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|code-block|syntax|javascript|2538539|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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
</code></pre>

blocks|key|2539055|text|如果您使用的是SQL+Server，则可以在不指定联接的情况下从一个表更新另一个表，并只需通过where子句将这两个表链接起来。这使得SQL查询变得简单得多：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2539056|UPDATE+Table1
SET+Table1.col1+=+Table2.col1,
++++Table1.col2+=+Table2.col2
FROM
++++Table2
WHERE
++++Table1.id+=+Table2.id|code-block|syntax|javascript|2539057|entityMap^0|1B|5|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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

<pre><code>UPDATE Table1
SET Table1.col1 = Table2.col1,
 Table1.col2 = Table2.col2
FROM
 Table2
WHERE
 Table1.id = Table2.id
</code></pre>

blocks|key|54817|text|在这里整合所有不同的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|54818|使用通用表expression|54819|Merge|unordered-list-item|54820|
选择|54821|更新|ordered-list-item|54822|54823|54824|示例表结构如下，它将从Product_BAK表更新为Product表。|54825|产品|54826|CREATE+TABLE+[dbo].[Product](
++++[Id]+[int]+IDENTITY(1,+1)+NOT+NULL,
++++[Name]+[nvarchar](100)+NOT+NULL,
++++[Description]+[nvarchar](100)+NULL
)+ON+[PRIMARY]|code-block|syntax|javascript|54827|Product_BAK|54828|++++CREATE+TABLE+[dbo].[Product_BAK](
++++++++[Id]+[int]+IDENTITY(1,+1)+NOT+NULL,
++++++++[Name]+[nvarchar](100)+NOT+NULL,
++++++++[Description]+[nvarchar](100)+NULL
++++)+ON+[PRIMARY]|54829|1.选择更新|54830|++++update+P1
++++set+Name+=+P2.Name
++++from+Product+P1
++++inner+join+Product_Bak+P2+on+p1.id+=+P2.id
++++where+p1.id+=+2|54831|2.使用公用表表达式进行更新|54832|++++;+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|54833|3.合并|54834|++++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;|54835|在这条Merge语句中，如果在目标中找不到匹配的记录，但存在于源中，则可以执行inset，请查找语法：|54836|++++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);|54837|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1J|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1K|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1L|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|1M|8|@]|9|@]|A|$]]|$1|L|3|J|5|K|7|1N|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1P|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|1R|8|@]|9|@]|A|$U|V]]|$1|W|3|X|5|6|7|1S|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|T|7|1T|8|@]|9|@]|A|$U|V]]|$1|10|3|11|5|6|7|1U|8|@]|9|@]|A|$]]|$1|12|3|13|5|T|7|1V|8|@]|9|@]|A|$U|V]]|$1|14|3|15|5|6|7|1W|8|@]|9|@]|A|$]]|$1|16|3|17|5|T|7|1X|8|@]|9|@]|A|$U|V]]|$1|18|3|19|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|T|7|1Z|8|@]|9|@]|A|$U|V]]|$1|1C|3|1D|5|6|7|20|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|T|7|21|8|@]|9|@]|A|$U|V]]|$1|1G|3|-4|5|6|7|22|8|@]|9|@]|A|$]]]|1H|$]]

Consolidating all the different approaches here.

<ol>
<li>Select update</li>
<li>Update with a common table expression</li>
<li>Merge</li>
</ol>

Sample table structure is below and will update from Product_BAK to Product table.

<h3>Product</h3>

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

<h3>Product_BAK</h3>

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

<h3>1. Select update</h3>

<pre><code> update P1
 set Name = P2.Name
 from Product P1
 inner join Product_Bak P2 on p1.id = P2.id
 where p1.id = 2
</code></pre>

<h3>2. Update with a common table expression</h3>

<pre><code> ; 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
</code></pre>

<h3>3. Merge</h3>

<pre><code> 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;
</code></pre>

In this Merge statement, we can do inset if not finding a matching record in the target, but exist in the source and please find syntax:

<pre><code> 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);
</code></pre>

blocks|key|54675|text|另一种方法是使用派生表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|54676|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|code-block|syntax|javascript|54677|54678|样本数据|54679|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|54680|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|O|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|P|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Q|8|@]|9|@]|A|$]]|$1|J|3|K|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|L|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|M|$]]

The other way is to use a derived table:

<pre><code>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
</code></pre>

<hr>

Sample data

<pre><code>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
</code></pre>

blocks|key|2538960|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2538961|要确保更新的是您想要的内容，请先选择|unstyled|2538962|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|2538963|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>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
</code></pre>

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

<pre><code>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
</code></pre>

blocks|key|2749087|text|甚至还有一个更短的方法，它可能会让您大吃一惊：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2749088|样本数据集：|2749089|CREATE+TABLE+#SOURCE+([ID]+INT,+[Desc]+VARCHAR(10));
CREATE+TABLE+#DEST+++([ID]+INT,+[Desc]+VARCHAR(10));

INSERT+INTO+#SOURCE+VALUES(1,'Desc_1'),+(2,+'Desc_2'),+(3,+'Desc_3');
INSERT+INTO+#DEST+++VALUES(1,'Desc_4'),+(2,+'Desc_5'),+(3,+'Desc_6');|code-block|syntax|javascript|2749090|代码：|2749091|UPDATE+#DEST
SET+#DEST.[Desc]+=+#SOURCE.[Desc]
FROM+#SOURCE
WHERE+#DEST.[ID]+=+#SOURCE.[ID];|2749092|entityMap^0|9|2|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

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

Sample data set:

<pre><code>CREATE TABLE #SOURCE ([ID] INT, [Desc] VARCHAR(10));
CREATE TABLE #DEST ([ID] INT, [Desc] VARCHAR(10));

INSERT INTO #SOURCE VALUES(1,'Desc_1'), (2, 'Desc_2'), (3, 'Desc_3');
INSERT INTO #DEST VALUES(1,'Desc_4'), (2, 'Desc_5'), (3, 'Desc_6');
</code></pre>

Code: 

<pre><code>UPDATE #DEST
SET #DEST.[Desc] = #SOURCE.[Desc]
FROM #SOURCE
WHERE #DEST.[ID] = #SOURCE.[ID];
</code></pre>

blocks|key|53526|text|使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53527|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|code-block|syntax|javascript|53528|以下任一项：|53529|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')|53530|或者：|53531|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|53532|如果两个表中的ID列名相同，则只需将表名放在要更新的表前，并为所选表使用别名，即：|53533|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')|53534|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Use:

<pre><code>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
</code></pre>

EITHER:

<pre><code>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')
</code></pre>

OR:

<pre><code>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
</code></pre>

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, i.e.:

<pre><code>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')
</code></pre>

blocks|key|53805|text|在被接受的答案中，在以下内容之后：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53806|SET
Table_A.col1+=+Table_B.col1,
Table_A.col2+=+Table_B.col2|code-block|syntax|javascript|53807|我要补充的是：|53808|OUTPUT+deleted.*,+inserted.*|53809|我通常做的是将所有内容放在回滚事务中，并使用"OUTPUT"：这样，我就可以看到即将发生的一切。当我对看到的内容感到满意时，我会将ROLLBACK更改为COMMIT。|offset|length|style|CODE|53810|我通常需要记录我所做的事情，所以我在运行回滚查询时使用"results+to+Text"选项，并保存脚本和输出结果。(当然，如果我更改了太多行，这是不实际的)|53811|entityMap^0|0|0|0|0|M|8|1T|8|24|6|0|R|H|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@$M|Z|N|10|O|P]|$M|11|N|12|O|P]|$M|13|N|14|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@$M|16|N|17|O|P]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|T|$]]

In the accepted answer, after the:

<pre><code>SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
</code></pre>

I would add:

<pre><code>OUTPUT deleted.*, inserted.*
</code></pre>

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

I usually need to document what I did, so I use the <code>"results to Text"</code> option when I run the roll-backed 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)

blocks|key|54563|text|以下解决方案适用于MySQL数据库：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|54564|UPDATE+table1+a+,+table2+b+
SET+a.columname+=+'some+value'+
WHERE+b.columnname+IS+NULL+;|code-block|syntax|javascript|54565|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The below solution works for a MySQL database:

<pre><code>UPDATE table1 a , table2 b 
SET a.columname = 'some value' 
WHERE b.columnname IS NULL ;
</code></pre>

blocks|key|2538683|text|UPDATE+table+AS+a
INNER+JOIN+table2+AS+b
ON+a.col1+=+b.col1
INNER+JOIN+...+AS+...
ON+...+=+...
SET+...
WHERE+...|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2538684|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>UPDATE table AS a
INNER JOIN table2 AS b
ON a.col1 = b.col1
INNER JOIN ... AS ...
ON ... = ...
SET ...
WHERE ...
</code></pre>

blocks|key|2538929|text|从select语句更新的另一种方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2538930|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'|code-block|syntax|javascript|2538931|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The other way to update from a select statement:

<pre><code>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'
</code></pre>

blocks|key|2539182|text|选项1:使用内连接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2539183|UPDATE
++++A
SET
++++A.col1+=+B.col1,
++++A.col2+=+B.col2
FROM
++++Some_Table+AS+A
++++INNER+JOIN+Other_Table+AS+B
++++++++ON+A.id+=+B.id
WHERE
++++A.col3+=+'cool'|code-block|syntax|javascript|2539184|选项2:+Co相关子查询|2539185|UPDATE+table+
SET+Col1+=+B.Col1,+
++++Col2+=+B.Col2+
FROM+(
++++SELECT+ID,+Col1,+Col2+
++++FROM+other_table)+B
WHERE+
++++B.ID+=+table.ID|2539186|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Option 1: Using Inner Join:

<pre><code>UPDATE
 A
SET
 A.col1 = B.col1,
 A.col2 = B.col2
FROM
 Some_Table AS A
 INNER JOIN Other_Table AS B
 ON A.id = B.id
WHERE
 A.col3 = 'cool'
</code></pre>

Option 2: Co related Sub query

<pre><code>UPDATE table 
SET Col1 = B.Col1, 
 Col2 = B.Col2 
FROM (
 SELECT ID, Col1, Col2 
 FROM other_table) B
WHERE 
 B.ID = table.ID
</code></pre>

blocks|key|53777|text|需要指出的是，与其他人一样，MySQL或MariaDB使用不同的语法。此外，它还支持一种非常方便的使用语法(与T/SQL相比)。此外，内部连接与连接是同义词。因此，原始问题中的查询最好在MySQL中实现，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|53778|UPDATE
++++Some_Table+AS+Table_A

JOIN
++++Other_Table+AS+Table_B+USING(id)

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

WHERE
++++Table_A.col3+=+'cool'|code-block|syntax|javascript|53779|我没有在其他答案中看到对所问问题的解决方案，因此我有两点看法。(在PHP7.4.0+MariaDB+10.4.10上测试)|53780|entityMap^0|E|5|K|7|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

Important to point out, as others have, MySQL or MariaDB use a different syntax. Also it supports a very convenient USING syntax (in contrast to T/SQL). Also INNER JOIN is synonymous with JOIN. Therefor the query in the original question would be best implemented in MySQL thusly:
<pre><code>UPDATE
 Some_Table AS Table_A

JOIN
 Other_Table AS Table_B USING(id)

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

WHERE
 Table_A.col3 = 'cool'
</code></pre>
I've not seen the a solution to the asked question in the other answers, hence my two cents.
(tested on PHP 7.4.0 MariaDB 10.4.10)

blocks|key|2539238|text|UPDATE+table1
SET+column1+=+(SELECT+expression1
+++++++++++++++FROM+table2
+++++++++++++++WHERE+conditions)
[WHERE+conditions];|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2539239|使用SQL+Server中另一个表中的数据更新一个表时UPDATE语句的语法|unstyled|2539240|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>UPDATE table1
SET column1 = (SELECT expression1
 FROM table2
 WHERE conditions)
[WHERE conditions];
</code></pre>

The syntax for the UPDATE statement when updating one table with data from another table in SQL Server

blocks|key|53832|text|您可以在sql+server中使用from+this+for+update。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53833|UPDATE
++++T1
SET
+++T1.col1+=+T2.col1,
+++T1.col2+=+T2.col2
FROM
+++Table1+AS+T1
INNER+JOIN+Table2+AS+T2
++++ON+T1.id+=+T2.id
WHERE
++++T1.col3+=+'cool'|code-block|syntax|javascript|53834|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use from this for update in sql server

<pre><code>UPDATE
 T1
SET
 T1.col1 = T2.col1,
 T1.col2 = T2.col2
FROM
 Table1 AS T1
INNER JOIN Table2 AS T2
 ON T1.id = T2.id
WHERE
 T1.col3 = 'cool'
</code></pre>

blocks|key|53283|text|declare+@tblStudent+table+(id+int,name+varchar(300))
declare+@tblMarks+table+(std_id+int,std_name+varchar(300),subject+varchar(50),marks+int)

insert+into+@tblStudent+Values+(1,'Abdul')
insert+into+@tblStudent+Values(2,'Rahim')

insert+into+@tblMarks+Values(1,'','Math',50)
insert+into+@tblMarks+Values(1,'','History',40)
insert+into+@tblMarks+Values(2,'','Math',30)
insert+into+@tblMarks+Values(2,'','history',80)


select+*+from+@tblMarks

update+m
set+m.std_name=s.name
+from+@tblMarks+as+m
left+join+@tblStudent+as+s+on+s.id=m.std_id

select+*+from+@tblMarks|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|53284|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>declare @tblStudent table (id int,name varchar(300))
declare @tblMarks table (std_id int,std_name varchar(300),subject varchar(50),marks int)

insert into @tblStudent Values (1,'Abdul')
insert into @tblStudent Values(2,'Rahim')

insert into @tblMarks Values(1,'','Math',50)
insert into @tblMarks Values(1,'','History',40)
insert into @tblMarks Values(2,'','Math',30)
insert into @tblMarks Values(2,'','history',80)


select * from @tblMarks

update m
set m.std_name=s.name
 from @tblMarks as m
left join @tblStudent as s on s.id=m.std_id

select * from @tblMarks
</code></pre>

blocks|key|2539251|text|我之前使用的是INSERT+SELECT，对于那些想要使用新东西的人，我会把这个解决方案放在工作相似但更短的地方：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2539252|UPDATE+table1+++++++++++++++++++++++++++++++++++++//table+that's+going+to+be+updated
LEFT+JOIN+++++++++++++++++++++++++++++++++++++++++//type+of+join
table2+AS+tb2+++++++++++++++++++++++++++++++++++++//second+table+and+rename+for+easy
ON
tb2.filedToMatchTables+=+table1.fieldToMatchTables//fileds+to+connect+both+tables
SET+++
fieldFromTable1+=+tb2.fieldFromTable2;++++++++++++//field+to+be+updated+on+table1

field1FromTable1+=+tb2.field1FromTable2,++++++++++//This+is+in+the+case+you+need+to
field1FromTable1+=+tb2.field1FromTable2,++++++++++//update+more+than+one+field
field1FromTable1+=+tb2.field1FromTable2;++++++++++//remember+to+put+;+at+the+end|code-block|syntax|javascript|2539253|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I was using INSERT SELECT Before, for those who want to use new stuff i will put this solution that works similar but much shorter:
<pre><code>UPDATE table1 //table that's going to be updated
LEFT JOIN //type of join
table2 AS tb2 //second table and rename for easy
ON
tb2.filedToMatchTables = table1.fieldToMatchTables//fileds to connect both tables
SET 
fieldFromTable1 = tb2.fieldFromTable2; //field to be updated on table1

field1FromTable1 = tb2.field1FromTable2, //This is in the case you need to
field1FromTable1 = tb2.field1FromTable2, //update more than one field
field1FromTable1 = tb2.field1FromTable2; //remember to put ; at the end
</code></pre>

blocks|key|2749230|text|同样的解决方案可以用稍微不同的方式编写，因为我只想在写完两个表之后设置列。在mysql中工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2749231|UPDATE+Table+t,+
(SELECT+col1,+col2+FROM+other_table+WHERE+sql+=+'cool'+)+o
SET+t.col1+=+o.col1,+t.col2=o.col2
WHERE+t.id+=+o.id|code-block|syntax|javascript|2749232|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The same solution can be written in a slightly different way as I would like to set the columns only once I have written about both the tables. Working in mysql.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

<pre><code>UPDATE Table t, 
(SELECT col1, col2 FROM other_table WHERE sql = 'cool' ) o
SET t.col1 = o.col1, t.col2=o.col2
WHERE t.id = o.id
</code></pre>

blocks|key|53377|text|公司中使用的最佳实践更新SQL+Server中的行和safe|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|53378|+WITH+t+AS+
+++++++++(
+++++++++++SELECT+UserID,+EmailAddress,+Password,+Gender,+DOB,+Location,+
+++++++++++Active++FROM+Facebook.Users
+++++++++)
+UPDATE+t+SET+Active+=+0|code-block|syntax|javascript|53379|这是更新记录的最安全的方式，您可以通过这种方式查看我们要更新的内容。来源：url|53380|entityMap|0|LINK|mutability|MUTABLE|url|https://www.iodocs.com/best-practice-update-rows-on-sql-server/^0|6|O|0|0|0|14|11|3|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@$9|11|A|12|1|13]]|E|$]]|$1|M|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

Best practices Update rows and safe in SQL Server used in the company
<pre><code> WITH t AS 
 (
 SELECT UserID, EmailAddress, Password, Gender, DOB, Location, 
 Active FROM Facebook.Users
 )
 UPDATE t SET Active = 0
</code></pre>
It is the safest way to update the records, and this is how you can see what we are going to update.
Source: <a href="https://www.iodocs.com/best-practice-update-rows-on-sql-server/" rel="nofollow noreferrer">url</a>

blocks|key|53957|text|Oracle+SQL+(使用别名)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53958|UPDATE+Table+T+
SET+T.col1+=+(SELECT+OT.col1+WHERE+OT.id+=+T.id),
T.col2+=+(SELECT+OT.col2+WHERE+OT.id+=+T.id);|code-block|syntax|javascript|53959|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Oracle SQL (use an alias):
<pre><code>UPDATE Table T 
SET T.col1 = (SELECT OT.col1 WHERE OT.id = T.id),
T.col2 = (SELECT OT.col2 WHERE OT.id = T.id);
</code></pre>

blocks|key|53315|text|就像这样；但是你必须确保update+table和from之后的table是一样的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|53316|UPDATE+Table+SET+col1,+col2
FROM+table
inner+join+other_table+Table.id+=+other_table.id
WHERE+sql+=+'cool'|code-block|syntax|javascript|53317|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

like this; but you must sure update table and table after from have be same.

<pre><code>UPDATE Table SET col1, col2
FROM table
inner join other_table Table.id = other_table.id
WHERE sql = 'cool'
</code></pre>

In SQL Server, it is possible to insert rows into a table with an <code>INSERT.. SELECT</code> statement:
<pre><code>INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3 
FROM other_table 
WHERE sql = 'cool'
</code></pre>
Is it also possible to update a table with <code>SELECT</code>? I have a temporary table containing the values and would like to update another table using those values. Perhaps something like this:
<pre><code>UPDATE Table SET col1, col2
SELECT col1, col2 
FROM other_table 
WHERE sql = 'cool'
WHERE Table.id = other_table.id
</code></pre>

How do I UPDATE from a SELECT in SQL Server?

在SQL Server中，可以使用INSERT.. SELECT语句将行插入到表中：INSERT INTO Table (col1, col2, col3)SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool'是否也可以使用SELECT更新表？我有一个包含这些值的临时表，并希望使用这些值更新另一个表。可能是这样的：UPDATE Tab

问如何在SQL Server中从SELECT更新？
EN

回答 37

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL Server中从SELECT更新？EN

回答 37

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL Server中从SELECT更新？
EN