带有2个INNER JOIN的MYSQL更新挂起 [英] MYSQL Update with 2 INNER JOINs hangs

问题描述

我正在尝试根据表中的字段是否存在于两个联接表中来对其进行更新.

I am trying to update a field in my table based on if it exists in two join tables.

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.

this query runs, but hangs. If I remove on inner join it works, but I need to check both join tables for the existance of the quote id.

推荐答案

要小心，因为使用JOIN可能会因为唯一的支持数据而导致重复数据的风险.我将您的查询改写为:

Have to be careful, because using JOINs risks duplicated data for the sake of the unique supporting data. I re-wrote your query as:

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:

Using 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

这篇关于带有2个INNER JOIN的MYSQL更新挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！