MYSQL Update with 2 INNER JOINs hangs

Question

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'

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.

Answer 1

My guess is that it's mainly due to the <> (NOT EQUALS) operator. Is that really what you're looking for here?

Answer 2

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')

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

Answer 3

Also, if you have a Quote record in both the joinging tables....you will get 2 records back. Seems that taking one of the joins out returns only one record to you. Try doing a select instead of the update on a certain Quote that exists in both tables and see if you get two records back. You will have to modify the joins down in order to get one quote record returned.