CODEWITHSUNDEEP
phpmysql
SystemX17
SystemX17

Reputation: 3715

How to run a MYSQL INSERT query that doesn't replace duplicates based on multiple columns where auto-increment id is present?

I have searched around for the answer to this and have come across IGNORE and WHERE NOT EXISTS however they both seem to do slightly differently than what I am trying to accomplish. My MYSQL table appears as follows:

id(auto increment INT), charactername(VARCHAR), characterregion(VARCHAR), characterrealm(VARCHAR)

My data is retrieved from a website that returns all of the characters of a game, even the ones I already have in my database.

I wish to keep a list of all of the characters but no duplicates. My issue seems to be that I need to compare the name, realm and region of the character before deciding if it is a duplicate as the same name can appear on different region/realm combinations.

I have tried comparing all of the values of the 3 non-auto incrementing columns as follows:

REPLACE INTO characters (charactername, characterregion, characterrealm) VALUES ('Peter','AMERICA','Realm1') WHERE NOT EXISTS(SELECT * FROM characters WHERE charactername='Peter' AND characterregion='AMERICA' AND characterrealm='Realm1')

This however returns a MYSQL error as the syntax is incorrect. I have also tried INSERT IGNORE INTO... however that only seems to be checking the id value. I don't believe I need to check the id at all as I have it set to auto increment.

Any help would be greatly appreciated, I am using PHP for the other parts of this if it helps. Thanks again.

Upvotes: 0

Views: 60

Answers (1)

Mihai
Mihai

Reputation: 26784

Just add a composite index on all 3 columns.

alter ignore table mytable add unique index(charactername, characterregion, characterrealm);

then do

INSERT INTO characters (charactername, characterregion, characterrealm)  
VALUES ('Peter','AMERICA','Realm1')  
ON DUPLICATE KEY 
UPDATE
charactername='Peter1',characterregion='AMERICA1',characterrealm='Realm11'

The update will trigger only if all 3 columns are identical.Or you could do just an INSERT and it will fail if all 3 columns are identical.

Upvotes: 1

Related Questions