mysql ON DUPLICATE KEY UPDATE, check 2 columns at a time

Question

Let's say there are 4 columns in my table

ID int(11) AI PK
BANK_ID int(11)
BANK_BRANCH_CODE varchar(255)
BANK_BRANCH_NAME varchar(255)

I want to UPDATE the BANK_BRANCH_NAME if the combination of BANK_ID and BANK_BRANCH_CODE already exist or else INSERT new data.

Here's what I got

INSERT INTO bank_table (BANK_ID, BANK_BRANCH_CODE, BANK_BRANCH_NAME) VALUES 
(bankId, bankBranchCode, bankBranchName) ON DUPLICATE KEY UPDATE BANK_BRANCH_NAME = bankBranchName;

But it seems like it only keeps inserting new data. Are there anything wrong with my query?

Answer 1

You'll need to add the UNIQUE constraint on the combination of BANK_ID and BANK_BRANCH_CODE. The ON DUPLICATE KEY UPDATE works only when there is a unique constraint or primary key violation.

ALTER TABLE bank_table
ADD UNIQUE (BANK_ID, BANK_BRANCH_CODE);
# other queries...

DBFIDDLE