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

0 comments:

Post a Comment