I am implementing a situation when the INSERT ...ON DUPLICATE KEY UPDATE needs to be executed only after a certain condition is satisfied. I am having issues on how to include the condition statement. Please guide me a work around for this.
I tried WHERE statement but insert...on duplicate key update doesn't support WHERE statement.
The implementation flow is like this:
table1(sl.no, unique_address, col1,col2, col3) primary key(sl.no, unique_address)
table2(fk_id,unique_address,col1,col2) Primary key(fk_id,unique_address) Foreign key (unique_address) REFERS table1(unique_address)
fk_id is not an auto_increment key
New unique entries with unique addresses e.g. (fk_id, 'KEY') should be inserted in the table2 only if there is a record for the 'KEY' in table1. Else, do not enter it in table 2. Only after that insert values('fk_id','KEY','value',null)(if the entry is not present in table2) or update(?,?,?,value)(if the entry is already present).
#Pseudo Code mysql 8.02
IF EXISTS('KEY' in (SELECT table1.unique_address FROM table1))
INSERT INTO table2 (fk_id,unique_address,col1,col2)
VALUES('fk_id', 'KEY','value', default)
ON DUPLICATE KEY UPDATE col2 = 'value';
#ELSE do nothing or skip to next iteration( if inside a loop)
END IF
Thanks in advance!
Aucun commentaire:
Enregistrer un commentaire