使用内连接更新 SQL

Question

我有一个记录表，它具有自我关系。

另外 - 为了使搜索更容易 - 我有一个标志，它确定一条记录已被引用，因此该行现在“已过时”，并且仅用于审核目的：

CREATE TABLE Records
(
  RecordID INT(5) NOT NULL,
  Replaces INT(5) NULL,
  Obsolete INT(1) NOT NULL
)

RecordID 是 PK，Replaces 链接到现已被替换的先前 RecordID，而 Obsolete 是冗余信息，它只是表示另一条记录已替换了该记录。它只是让搜索变得更加容易。桌子很大。这些只是其中的 3 列。

唯一的问题是：系统中的一个查询存在拼写错误，因此对于一小部分行，Obsolete 值未设置为 1 (true)。

此查询将显示 Obsolete 等于 0 的所有记录，应等于 1：

   SELECT *
     FROM Records AS rec1
LEFT JOIN Records AS rec2
       ON rec1.Replaces = rec2.RecordID
    WHERE rec2.RecordID IS NOT NULL
      AND rec2.Obsolete = 0;

现在我需要运行 UPDATE 将所有这些 req2.Obsolete 从 0 更改为 1，但我不确定如何编写查询内部连接。

Answer 1

您不需要内部联接。由于您的查询已经返回需要更新的记录，只需执行以下操作：

Update Records
set Obsolete=1 where
RecordID in (
 SELECT rec2.RecordID     
        FROM Records AS rec1
LEFT JOIN Records AS rec2
       ON rec1.Replaces = rec2.RecordID
    WHERE rec2.RecordID IS NOT NULL
      AND rec2.Obsolete = 0
)

Answer 2

UPDATE Records
SET obsolete = 1
WHERE recordID in (
SELECT rec1.recordid
     FROM Records AS rec1
LEFT JOIN Records AS rec2
       ON rec1.Replaces = rec2.RecordID
    WHERE rec2.RecordID IS NOT NULL
      AND rec2.Obsolete = 0
)

Answer 3

我建议使用临时表分两步执行此操作：

-- Create temporary table for holding RecordIDs to be marked as obsolete
CREATE TEMPORARY TABLE `mark_obsolete` (`RecordID` INT NOT NULL);

-- Insert RecordIDs to mark as obsolete into temp table
INSERT INTO `mark_obsolete` (`RecordID`)
SELECT `rec2`.`RecordID`
FROM
    `Records` AS `rec1`
    INNER JOIN `Records` AS `rec2`
        ON `rec1`.`Replaces` = `rec2`.`RecordID`
WHERE `rec2`.`Obsolete` = 0;

-- Update records using inner join to temp table
UPDATE
    `Records` AS `r`
    INNER JOIN `mark_obsolete` AS `o`
        ON `r`.`RecordID` = `o`.`RecordID`
SET `r`.`Obsolete` = 1;

DROP TEMPORARY TABLE `mark_obsolete`;

请注意，使用 LEFT JOIN 与 WHERE rec2.RecordID IS NOT NULL 与 INNER 相同加入。

使用临时表的原因是为了避免在更新子查询中使用的同一表时出现锁定问题。与使用 IN 子句相比，它还可能为您提供更好的性能。