题 如何在SQL中使用JOIN执行UPDATE语句?


我需要更新此表 SQL Server 2005 使用其“父”表中的数据,请参见下文:

拍卖

id (int)
udid (int)
assid (int)

UD

id  (int)
assid  (int)

sale.assid 包含要更新的正确值 ud.assid

什么查询会这样做?我在想 join 但我不确定这是否可能。


1025
2017-08-18 11:40


起源


您使用的是什么RDBMS? MySQL,SQL Server,Oracle,PostgreSQL还是其他什么? - Chris J
表之间有一些关系吗?如何知道哪个销售记录对应于来自ud的记录?它是否基于id作为两个表中的主键? - Cătălin Pitiș
你怎么更新UD?它只有assid和它自己的ID。您能举例说明存在的实际值,以及您希望由于脚本而更改或添加的记录吗? - Bernhard Hofmann
另见SO问题...... stackoverflow.com/questions/982919/sql-update-query-using-joins - SteveC
查询中的用户别名如 stackoverflow.com/questions/982919/sql-update-query-using-joins - Imran Muhammad


答案:


语法严格依赖于您正在使用的SQL DBMS。以下是ANSI / ISO(也称应适用于任何SQL DBMS),MySQL,SQL Server和Oracle的一些方法。请注意,我建议的ANSI / ISO方法通常比其他两种方法慢得多,但如果你使用的是MySQL,SQL Server或Oracle之外的SQL DBMS,那么它可能是唯一的方法(例如如果您的SQL DBMS不支持 MERGE):

ANSI / ISO:

update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid = ud.id
     )
 where exists (
      select * 
      from sale 
      where sale.udid = ud.id
 );

MySQL的:

update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid

SQL Server:

update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

甲骨文:

update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid

SQLite的:

update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid = ud.id
     )
 where RowID in (
      select RowID 
      from ud 
      where sale.udid = ud.id
 );

1926
2017-08-18 11:44



它在我看来是MySQL set assid = s.assid 应该 set u.assid = s.assid。 - dotancohen
你能解释一下SQLite吗? - Prabakaran Raja
在ANSI语法中,如果SELECT之后会发生什么 = 返回多行? - Throwaway Account 3 Million
@ ThrowawayAccount3Million它可能会失败。 AFAIK,这种操作会期望标量值,如果给定结果集,则会抛出错误。 - Francis Lord
@PrabakaranRaja, ANSI/ISO 答案适用于 sqlite3。 - toto_tico


这应该在SQL Server中工作:

update ud 
set assid = sale.assid
from sale
where sale.udid = id

109
2017-08-18 11:42



谢谢,这对我来说似乎是MSSQL最优雅的解决方案。 - Patrick_870206


Postgres的

UPDATE table1
SET    COLUMN = value
FROM   table2,
       table3
WHERE  table1.column_id = table2.id
       AND table1.column_id = table3.id
       AND table1.COLUMN = value
       AND table2.COLUMN = value
       AND table3.COLUMN = value 

81
2018-01-17 13:53



没关系,但不是ANSI :( - makciook
如果它将使用问题中使用的表/列名称,答案会更方便。为什么答案中有3个表格? - alfonx


标准的SQL方法是

UPDATE ud
SET assid = (SELECT assid FROM sale s WHERE ud.id=s.id)

在SQL Server上,您可以使用连接

UPDATE ud
SET assid = s.assid
FROM ud u
JOIN sale s ON u.id=s.id

38
2017-08-18 11:44



对于第一个,你不能匹配2+列,但加入效果很好。 - makciook
@makciook:嗯?你可以在中添加更多条件 WHERE 如果要匹配其他列,则使用子句。 - siride
只是一个人......但我认为OP意味着sale.udid = ud.id.而不是sale.id. - Skippy VonDrake
第一个在Oracle SQL中为我工作。 - Fabian Röling


PostgreSQL的

CREATE TABLE ud (id integer, assid integer);
CREATE TABLE sales (id integer, udid integer, assid integer);

UPDATE ud
SET assid = sales.assid
FROM sales
WHERE sales.id = ud.id;

26
2018-03-07 21:35



这是唯一一个适用于PostgreSQL的程序 - Almog Cohen


使用简化的更新查询 加入 - 多个表。

   UPDATE
        first_table ft
        JOIN second_table st ON st.some_id = ft.some_id
        JOIN third_table tt  ON tt.some_id = st.some_id
        .....
    SET
        ft.some_column = some_value
    WHERE ft.some_column = 123456 AND st.some_column = 123456

注意  - first_table,second_table,third_table和some_column(如123456)是演示表名,列名和ID。用有效名称替换它们。


17
2017-07-14 07:15



什么相当于在oracle? - user1614862
对不起,我对oracle一无所知。 - Vinit Kadkol


另一个例子为什么SQL不是真正可移植的。

对于MySQL,它将是:

update ud, sale
set ud.assid = sale.assid
where sale.udid = ud.id;

有关更多信息,请阅读多表更新: http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

11
2017-11-16 19:17



关于“为什么SQL不是真正可移植”的评论+1!可移植性是如此脆弱,只是声明一个变量将破坏许多流行的数据库引擎之间的可移植性。 - Jeff Moden


Teradata Aster提供了另一种有趣的方法来实现目标:

MERGE INTO ud --what trable should be updated
USING sale -- from what table/relation update info should be taken
ON ud.id = sale.udid --join condition
WHEN MATCHED THEN 
    UPDATE SET ud.assid = sale.assid; -- how to update

7
2017-07-03 10:22





我认为顶级帖子中的SQL-Server可以用于Sybase,因为它们都是T-SQL,但遗憾的是没有。

对于Sybase,我发现更新需要在表本身而不是别名:

update ud
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

6
2017-11-19 08:24





使用FROM关键字的以下语句用于使用连接更新多个行

UPDATE users 
set users.DivisionId=divisions.DivisionId
from divisions join users on divisions.Name=users.Division

4
2018-04-06 11:34