在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_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
其他回答
使用别名：
UPDATE t
SET t.col1 = o.col1
FROM table1 AS t
INNER JOIN
table2 AS o
ON t.id = o.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
如果使用MySQL而不是SQL Server，语法为：
UPDATE Table1
INNER JOIN Table2
ON Table1.id = Table2.id
SET Table1.col1 = Table2.col1,
Table1.col2 = Table2.col2
UPDATE YourTable
SET Col1 = OtherTable.Col1,
Col2 = OtherTable.Col2
FROM (
SELECT ID, Col1, Col2
FROM other_table) AS OtherTable
WHERE
OtherTable.ID = YourTable.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.
