在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
当前回答
我添加这个只是为了让你可以看到一个快速的方法来编写它，这样你就可以在更新之前检查将要更新的内容。
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
其他回答
同样的解决方案可以用稍微不同的方式编写，因为我只想设置一次列。这两张桌子我都写过了。它在MySQL中工作。
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
选项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
我以前使用过INSERT SELECT。对于那些想使用新东西的人来说，这里有一个类似的解决方案，但它要短得多：
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 // Fields 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.
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
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);
