mysql - SQL更新表困惑？

Question

我有两个表， communication (有一个列 timestamp、FromIDNumber 和 FromX )和 commLocation(具有列 timestamp、FromIDNumber 和 x)。

当两个表具有相同的 timestamp 和 FromIDNumber 时，我想设置 communication.FromX = commLocation.x。

我尝试这样做:

UPDATE communication
SET FromX=(SELECT commLocation.x
           FROM commLocation 
           JOIN communication
           ON communication.Timestamp=commLocation.timestamp
           WHERE communication.FromIDNumber=commLocation.FromIDNumber);

但是我得到了一个错误:

You can't specify target table 'communication' for update in FROM clause 

我该如何实现这个目标？这是SQL Fiddle 为它。现在，我正在表的末尾插入 FromX 值，但我不希望这样做，因为我需要它对应于 communication 的某一行....我该怎么做？

任何帮助将不胜感激，谢谢!

Answer 1

您可以在 UPDATE 语句中执行 JOIN 操作。

例如:

  UPDATE communication c
    JOIN commLocation l
      ON l.timestamp    = c.timestamp
     AND l.fromidnumber = c.fromidnumber
     SET c.fromx = l.x

这假设 (timestamp,fromidnumber) 在 commLocation 中是唯一的。

<小时/>

为了开发这样的查询，我们通常从 SELECT 语句开始......

  SELECT c.timestamp 
       , c.fromidnumber
       , c.fromx        AS old_fromx
       , l.x            AS new_fromx
    FROM communication c
    JOIN commLocation l
      ON l.timestamp    = c.timestamp
     AND l.fromidnumber = c.fromidnumber

SELECT 中的 new_fromx 是我们要分配给该列的表达式/值。我们可以在 SELECT 列表中返回其他列，以验证该语句正在执行我们希望它执行的操作。

一旦我们开始工作，我们就会通过删除 SELECT ... FROM 并将其替换为 UPDATE 将其转换为更新。并在 WHERE 子句之前添加一个 SET 子句。