在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
当前回答
选项1：使用内部联接：
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'
选项2：共同相关子查询
UPDATE table
SET Col1 = B.Col1,
Col2 = B.Col2
FROM (
SELECT ID, Col1, Col2
FROM other_table) B
WHERE
B.ID = table.ID
其他回答
另一种方法是使用派生表：
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
样本数据
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
对于记录（以及其他像我一样的搜索），您可以在MySQL中这样做：
UPDATE first_table, second_table
SET first_table.color = second_table.color
WHERE first_table.id = second_table.foreign_id
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
甚至还有一种更短的方法，可能会让你感到惊讶：
示例数据集：
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');
代码：
UPDATE #DEST
SET #DEST.[Desc] = #SOURCE.[Desc]
FROM #SOURCE
WHERE #DEST.[ID] = #SOURCE.[ID];
下面是另一个有用的语法：
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);
它使用“WHERE EXIST”检查它是否为空。
