Showing posts with label Mysql Update with Join. Show all posts
Showing posts with label Mysql Update with Join. Show all posts

Wednesday, 27 May 2015

MySQL update single table with join

I have a values in table "REVIEW" that need to change REVIEW_STATUS from "UNDER_REVIEW" to "Abstain" based upon other records in the table that have the same REFERENCE_NUMBER , A specific REVIEW_TYPE and that REVIEW_STATUS is "ABSTAIN".

Solution:
It was the word TABLE
UPDATE  REVIEW AS REV1  
LEFT JOIN REVIEW AS REV2 
ON (REV1.REFERENCE_NUMBER=REV2.REFERENCE_NUMBER)
SET REV1.REVIEW_STATUS='ABSTAIN' 
WHERE 
REV1.REVIEW_TYPE ='QOC' 
AND 
REV1.REVIEW_STATUS='UNDER_REVIEW'
AND 
REV2.REVIEW_TYPE ='MED_NEC'
AND (REV2.REVIEW_STATUS ='ABSTAIN' );