带有 2 个 INNER JOIN 的 MYSQL 更新挂起

Question

我正在尝试根据表中的字段是否存在于两个连接表中来更新该字段。

update quotes
inner join collection_records ON quotes.id <> collection_records.record_id
inner join subcollection_records ON quotes.id <> subcollection_records.record_id
set quotes.status_id = 1
where collection_records.type = 'Quote' 
   or subcollection_records.type = 'Quote'

此查询运行，但挂起。如果我删除内部联接，它可以工作，但我需要检查两个联接表是否存在报价 id。

Answer 1

必须小心，因为为了唯一的支持数据而使用 JOIN 会带来重复数据的风险。我将您的查询重写为:

UPDATE QUOTES
   SET status_id = 1
 WHERE id NOT IN (SELECT cr.record_id
                    FROM COLLECTION_RECORDS cr
                    WHERE cr.type = 'Quote')
   AND id NOT IN (SELECT sr.record_id
                    FROM SUBCOLLECTION_RECORDS sr
                   WHERE sr.type = 'Quote')

使用 LEFT JOIN/IS NULL:

UPDATE QUOTES
LEFT JOIN COLLECTION_RECORDS cr ON cr.record_id = id
                               AND cr.type = 'Quote'
LEFT JOIN SUBCOLLECTION_RECORDS sr ON sr.record_id = id
                                  AND sr.type = 'Quote'
  SET status_id = 1
WHERE cr.record_id IS NULL
  AND sr.record_id IS NULL