在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
当前回答
在SQL Server 2008（或更高版本）中，使用MERGE
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;
或者：
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;
其他回答
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
Use:
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
或者：
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')
OR:
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
如果两个表中的ID列名相同，则只需将表名放在要更新的表之前，并为所选表使用别名，即：
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')
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;
我添加这个只是为了让你可以看到一个快速的方法来编写它，这样你就可以在更新之前检查将要更新的内容。
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
以下示例使用派生表（FROM子句后的SELECT语句）返回旧值和新值以供进一步更新：
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
