在SQL Server中，可以使用insert将行插入到表中。。SELECT语句：
INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
是否也可以使用SELECT更新表？我有一个包含这些值的临时表，并希望使用这些值更新另一个表。也许是这样的：
UPDATE Table SET col1, col2
SELECT col1, col2
FROM other_table
WHERE sql = 'cool'
WHERE Table.id = other_table.id
当前回答
整合所有不同的方法。
选择更新使用公共表表达式更新合并
示例表结构如下，将从Product_BAK更新为Product表。
表产品
CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1, 1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Description] [nvarchar](100) NULL
) ON [PRIMARY]
表产品_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.选择更新
update P1
set Name = P2.Name
from Product P1
inner join Product_Bak P2 on p1.id = P2.id
where p1.id = 2
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 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;
在这个Merge语句中，如果在目标中找不到匹配的记录，但在源中存在，我们可以插入，请查找语法：
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);
其他回答
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'
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
要确保正在更新所需内容，请先选择
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
如果您使用的是SQL Server，则可以在不指定联接的情况下从另一个表更新一个表，并且只需从where子句链接这两个表。这使得SQL查询更加简单：
UPDATE Table1
SET Table1.col1 = Table2.col1,
Table1.col2 = Table2.col2
FROM
Table2
WHERE
Table1.id = Table2.id
Oracle SQL（使用别名）：
UPDATE Table T
SET T.col1 = (SELECT OT.col1 WHERE OT.id = T.id),
T.col2 = (SELECT OT.col2 WHERE OT.id = T.id);
SQLite3对我很有用，在INNER SELECT之后用SELECT更新行。
UPDATE clients
SET col1 = '2023-02-02 18:51:30.826621'
FROM (
SELECT * FROM clients dc WHERE dc.phone NOT IN (
SELECT do.phone FROM dclient_order do WHERE do.order_date > '2023-01-01' GROUP BY do.phone
)
) NewTable
WHERE clients.phone = NewTable.phone;
