1

I get the above error while I execute the below query

UPDATE jobs AS j1 SET j1.job_last_status = 10, j1.updated_at = '2020-05-04 09:59:11'
WHERE j1.id IN (SELECT jh.job_id
FROM jobs_history AS jh 
INNER JOIN jobs j ON j.id = jh.job_id AND j.job_last_status =7 
WHERE jh.job_status = 7 
GROUP BY jh.job_id 
HAVING COUNT(jh.job_id) =2) 
1
  • Don't know. But you don't need the alias in your update statement. UPDATE jobs SET job_last_status = 10, updated_at = '2020-05-04 09:59:11' WHERE id IN (...) will work just fine. Commented May 5, 2020 at 16:43

2 Answers 2

1
UPDATE jobs j1, ( SELECT jh.job_id
                  FROM jobs_history AS jh 
                  INNER JOIN jobs j ON j.id = jh.job_id 
                                   AND j.job_last_status =7 
                  WHERE jh.job_status = 7 
                  GROUP BY jh.job_id 
                  HAVING COUNT(jh.job_id) = 2 ) j2
SET j1.job_last_status = 10, 
    j1.updated_at = '2020-05-04 09:59:11'
WHERE j1.id = j2.job_id

or the same in JOIN form:

UPDATE jobs j1
  JOIN ( SELECT jh.job_id
         FROM jobs_history AS jh 
         INNER JOIN jobs j ON j.id = jh.job_id 
                          AND j.job_last_status =7 
         WHERE jh.job_status = 7 
         GROUP BY jh.job_id 
         HAVING COUNT(jh.job_id) = 2 ) j2 ON j1.id = j2.job_id
SET j1.job_last_status = 10, 
    j1.updated_at = '2020-05-04 09:59:11'
0

make a derived table

UPDATE jobs AS j1 SET j1.job_last_status = 10, j1.updated_at = '2020-05-04 09:59:11'
WHERE j1.id IN (SELECT jh.job_id
FROM jobs_history AS jh 
INNER JOIN (SELECT id FROM jobs WHERE job_last_status =7) j ON j.id = jh.job_id 
WHERE jh.job_status = 7 
GROUP BY jh.job_id 
HAVING COUNT(jh.job_id) =2) 

I can be that you have to include more comuns form the derived table j, but i can not see taht you need it it here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.