ChatGPT解决这个技术问题 Extra ChatGPT

如何从 SQL Server 中的 SELECT 更新?

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
提问的初衷是什么?特定于 Microsoft 的 SQL Server (T-SQL)?还是一个通用的 SQL 问题?

D
Dai
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'

如果您正在编辑表之间的链接 (SET Table.other_table_id = @NewValue),请将 ON 语句更改为 ON Table.id = @IdToEdit AND other_table.id = @NewValue
@CharlesWood 是的。我在 MySQL 中有同样的问题。如果有人知道如何将它实现到 MySQL 并与大家分享,那就太好了。相信很多人都在寻找 MySQL 版本的解决方案
如何在集合中使用别名?更新表集合 a.col1 = b.col2 从表 a 内连接 table2 b on a.id = b.id;相反,我必须使用 update table set table.col1 = b.col2 from table a inner join table2 b on a.id = b.id;
有点相关,我经常喜欢先将我的 UPDATE 查询写成 SELECT 语句,这样我就可以在执行之前看到将要更新的数据。 Sebastian 在最近的一篇博文中介绍了一种技术:sqlity.net/en/2867/update-from-select
您不能执行 SET Table_A.col1 = SUM(Table_B.col1) 或任何其他聚合。 Jamal 的回答允许您将聚合放在 SELECT stackoverflow.com/a/8963158/695671
b
baltermia

在 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;

MERGE 也可用于“Upserting”记录;也就是说,UPDATE 如果匹配记录存在,INSERT 新记录如果没有找到匹配
对我来说,这比等效的 update...join 语句快了大约 10 倍。
MERGE 也可以用来删除。但要小心 MERGE,因为 TARGET 表不能是远程表。
@SimonD:选择任何 SQL Server 关键字,您都会发现错误。你的观点?我敢打赌,与 UPDATE 相关的错误(以及更基本的错误)比 MERGE 更多,人们刚刚学会与他们相处,他们成为景观的一部分(“功能”)。考虑到当 UPDATE 是新来的孩子时,博客还不存在。
D
DDiamond
UPDATE YourTable 
SET Col1 = OtherTable.Col1, 
    Col2 = OtherTable.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) AS OtherTable
WHERE 
    OtherTable.ID = YourTable.ID

这往往适用于几乎所有 DBMS,这意味着一次学习,到处执行。如果这对您来说比性能更重要,您可能更喜欢这个答案,特别是如果您的更新是为了更正某些数据而一次性完成的。
如果您需要使用第二个表的聚合设置第一个表,您可以将聚合放在 select 子查询中,因为您不能执行 SET Table_A.col1 = SUM(Table_B.col1)(或任何其他聚合函数)。为此目的,比罗宾戴的答案要好。
我真的很喜欢这个解决方案,因为它感觉像是对 INSERT ... SELECT 工作方式的自然赞美。感谢分享!
P
Peter Mortensen

我会将 Robin's excellent answer 修改为以下内容:

UPDATE Table
SET Table.col1 = other_table.col1,
 Table.col2 = other_table.col2
FROM
    Table
INNER JOIN other_table ON Table.id = other_table.id
WHERE
    Table.col1 != other_table.col1
OR Table.col2 != other_table.col2
OR (
    other_table.col1 IS NOT NULL
    AND Table.col1 IS NULL
)
OR (
    other_table.col2 IS NOT NULL
    AND Table.col2 IS NULL
)

如果没有 WHERE 子句,您甚至会影响不需要影响的行,这可能(可能)导致索引重新计算或触发真正不应该触发的触发器。


这假设没有任何列可以为空。
你是对的,我是手动输入示例的。我在 where 语句中添加了第三个和第四个子句来处理这个问题。
WHERE EXISTS(SELECT T1.Col1, T1.Col2 EXCEPT SELECT T2.Col1, T2.Col2)) 更简洁。
语句不应该在 where 子句中也包含这两个吗? (other_table.col1 为空且 table.col1 不为空)或(other_table.col2 为空且 table.col2 不为空)
取决于您是否想用源中的空值替换目标中的空值。很多时候,我不会。但是如果你这样做了,Martin 对 where 子句的构造是最好的使用方法。
s
shA.t

单程

UPDATE t 
SET t.col1 = o.col1, 
    t.col2 = o.col2
FROM 
    other_table o 
  JOIN 
    t ON t.id = o.id
WHERE 
    o.sql = 'cool'

S
Stewart

尚未提及的另一种可能性是将 SELECT 语句本身放入 CTE,然后更新 CTE。

WITH CTE
     AS (SELECT T1.Col1,
                T2.Col1 AS _Col1,
                T1.Col2,
                T2.Col2 AS _Col2
         FROM   T1
                JOIN T2
                  ON T1.id = T2.id
         /*Where clause added to exclude rows that are the same in both tables
           Handles NULL values correctly*/
         WHERE EXISTS(SELECT T1.Col1,
                             T1.Col2
                       EXCEPT
                       SELECT T2.Col1,
                              T2.Col2))
UPDATE CTE
SET    Col1 = _Col1,
       Col2 = _Col2;

这样做的好处是,很容易首先单独运行 SELECT 语句以检查结果,但如果它们在源表和目标表中的名称相同,则需要您按上述方式对列进行别名。

这也与其他四个答案中显示的专有 UPDATE ... FROM 语法具有相同的限制。如果源表位于一对多连接的多端,则不确定将在 Update 中使用哪个可能的匹配连接记录(如果存在,MERGE 通过引发错误来避免这个问题)是尝试多次更新同一行)。


CTE 这个名字有什么含义吗?
@ShivanRaptor - 它是 Common Table Expression 的首字母缩写。在这种情况下只是一个任意别名。
这也适用于多个 CTE:;WITH SomeCompexCTE AS (...), CTEAsAbove AS (SELECT T1.Col1,... FROM T1 JOIN SomeComplexCTE...) UPDATE CTEAsAbove SET Col1=_Col1, ...
A
Adrian Macneil

作为记录(以及其他像我一样搜索的人),您可以在 MySQL 中这样做:

UPDATE first_table, second_table
SET first_table.color = second_table.color
WHERE first_table.id = second_table.foreign_id

r
rageit

使用别名:

UPDATE t
   SET t.col1 = o.col1
  FROM table1 AS t
         INNER JOIN 
       table2 AS o 
         ON t.id = o.id

S
Shiva

简单的方法是:

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

这不是 SQl Server 语法,它在 SQL Server 中不起作用
R
Ryan

这可能是执行更新的一个利基原因(例如,主要用于过程中),或者对其他人来说可能很明显,但也应该说明您可以在不使用连接的情况下执行更新选择语句(如果您正在更新的表没有公共字段)。

update
    Table
set
    Table.example = a.value
from
    TableExample a
where
    Table.field = *key value* -- finds the row in Table 
    AND a.field = *key value* -- finds the row in TableExample a

我觉得这应该是公认的答案,因为它使事情变得简单明了。
P
Peter Mortensen

这是另一个有用的语法:

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”检查它是否为空。


唯一对我有用的答案。我正在使用 MariaDB,我的查询格式为:SELECT value FROM table_1 INNER JOIN ( SELECT * FROM table_2 WHERE ..... ) ON ( ...... )
H
HLGEM

我添加它只是为了让您可以看到一种快速编写它的方法,以便您可以在更新之前检查将要更新的内容。

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 

S
Simon Hughes

如果您使用 MySQL 而不是 SQL Server,则语法为:

UPDATE Table1
INNER JOIN Table2
ON Table1.id = Table2.id
SET Table1.col1 = Table2.col1,
    Table1.col2 = Table2.col2

如果我们想更新 Table2.col1 怎么办?我们将如何做到这一点。表二是根据查询条件提取的。
B
BSMP

在 SQL 数据库中使用 INNER JOIN 从 SELECT 更新

由于这篇文章的回复太多,而且投票率最高,我想我也会在这里提出我的建议。虽然这个问题很有趣,但我在很多论坛网站上看到并使用带有屏幕截图的 INNER JOIN 做出了解决方案。

起初,我创建了一个名为 schoolold 的表,并插入了一些关于它们的列名的记录并执行它。

然后我执行 SELECT 命令来查看插入的记录。

https://i.stack.imgur.com/OdtDP.png

然后我创建了一个名为 schoolnew 的新表,并在其上执行了类似的操作。

https://i.stack.imgur.com/QP7Fw.png

然后,要查看其中插入的记录,我执行 SELECT 命令。

https://i.stack.imgur.com/BfqC5.png

现在,在这里我想对第三行和第四行进行一些更改,为了完成这个动作,我使用 INNER JOIN 执行 UPDATE 命令。

https://i.stack.imgur.com/agP7W.png

要查看更改,我执行 SELECT 命令。

https://i.stack.imgur.com/ws0Xv.png

您可以通过使用带有 UPDATE 语句的 INNER JOIN 来查看表 schoolold 的第三和第四条记录如何轻松地替换为表 schoolnew。


j
jakubiszon

如果您想自己加入表格(这不会经常发生):

update t1                    -- just reference table alias here
set t1.somevalue = t2.somevalue
from table1 t1               -- these rows will be the targets
inner join table1 t2         -- these rows will be used as source
on ..................        -- the join clause is whatever suits you

+1,但您应该使用相关的别名,例如 targett1sourcet1 而不是(或以及)评论。
P
Peter Mortensen

通过 CTE 更新比此处的其他答案更具可读性:

;WITH cte
     AS (SELECT col1,col2,id
         FROM   other_table
         WHERE  sql = 'cool')
UPDATE A
SET    A.col1 = B.col1,
       A.col2 = B.col2
FROM   table A
       INNER JOIN cte B
               ON A.id = B.id

P
Peter Mortensen

以下示例使用派生表(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

P
Peter Mortensen

如果您使用的是 SQL Server,您可以在不指定连接的情况下从另一个表更新一个表,只需从 where 子句链接这两个表。这使得 SQL 查询更加简单:

UPDATE Table1
SET Table1.col1 = Table2.col1,
    Table1.col2 = Table2.col2
FROM
    Table2
WHERE
    Table1.id = Table2.id

P
Peter Mortensen

在这里整合所有不同的方法。

选择更新 使用公用表表达式更新更新 Merge

示例表结构如下,将从 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]

餐桌产品_比克

    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);

“请找出语法”是什么意思?你能详细说明吗?
P
Peter Mortensen

另一种方法是使用派生表:

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

Y
Yaman
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

B
Bartosz X

甚至还有一个更短的方法,它可能会让您感到惊讶:

样本数据集:

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];

是 - 没有故意加入,不 - 这不能应用于表变量。
我认为,如果您在#SOURCE 上使用 [_id] 而不是与 #DESTINATION 相同的 [ID],他们可能会让您加入。 “在#DESTINATION.ID=#SOURCE._id。或者甚至使用像@tbl这样的表变量,”在PermTable.ID=@memorytbl._id上”。你试过吗?我用手机回复这个,没有电脑可以尝试.
这与从 SELECT 更新有什么关系?
这是相同的想法,但另一种方法 - 您根本不必在 update 语句中输入“select”来实现 JOIN 和 WHERE - 这是 SELECT 类型的查询,甚至无需编写 SELECT
P
Peter Mortensen

利用:

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')

或者:

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')

J
Johannes Wentu

在接受的答案中,在:

SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2

我要补充:

OUTPUT deleted.*, inserted.*

我通常做的是将所有内容放在回滚事务中并使用 "OUTPUT":通过这种方式,我可以看到即将发生的所有事情。当我对所看到的内容感到满意时,我将 ROLLBACK 更改为 COMMIT

我通常需要记录我所做的事情,因此我在运行回滚查询时使用 "results to Text" 选项并保存脚本和 OUTPUT 的结果。 (当然,如果我更改了太多行,这不切实际)


C
Cornezuelo del Centeno
UPDATE table AS a
INNER JOIN table2 AS b
ON a.col1 = b.col1
INNER JOIN ... AS ...
ON ... = ...
SET ...
WHERE ...

P
Peter Mortensen

以下解决方案适用于 MySQL 数据库:

UPDATE table1 a , table2 b 
SET a.columname = 'some value' 
WHERE b.columnname IS NULL ;

P
Peter Mortensen

从 select 语句更新的另一种方法:

UPDATE A
SET A.col = A.col,B.col1 = B.col1
FROM  first_Table AS A
INNER JOIN second_Table AS B  ON A.id = B.id WHERE A.col2 = 'cool'

这个答案出现在低质量审查队列中,大概是因为您没有提供任何代码解释。如果此代码回答了问题,请考虑在答案中添加一些解释代码的文本。这样一来,您就更有可能获得更多支持——并帮助提问者学习新知识。
J
Jesse

选项 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:Co相关子查询

UPDATE table 
SET Col1 = B.Col1, 
    Col2 = B.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) B
WHERE 
    B.ID = table.ID

它对你有用吗?我使用了完全相同的查询,但在使用内部联接时出现错误,无法解析别名。然而,共同相关的子查询工作得很好。
我没有确切的错误日志,但在分配之前引用了别名 A,这导致了错误。
我使用了相关的子查询
这是在什么上测试的? SQL ServerMySQL?还有什么?什么版本?
t
theking2

重要的是要指出,与其他人一样,MySQL 或 MariaDB 使用不同的语法。它还支持非常方便的 USING 语法(与 T/SQL 相比)。 INNER JOIN 也是 JOIN 的同义词。因此,原始问题中的查询最好在 MySQL 中实现:

UPDATE
    Some_Table AS Table_A

JOIN
    Other_Table AS Table_B USING(id)

SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2

WHERE
    Table_A.col3 = 'cool'

我没有在其他答案中看到所提问题的解决方案,因此我的两分钱。 (在 PHP 7.4.0 MariaDB 10.4.10 上测试)


重新“原始问题中的查询”:什么?第一个修订版带有 SQL Server(Microsoft 产品)的标签。
P
Peter Mortensen
UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

使用 SQL Server 中另一表的数据更新一个表时 UPDATE 语句的语法。