CODEWITHSUNDEEP
mysql
brewphone
brewphone

Reputation: 1356

MySql when Insert Duplicate, Replace or Ignore under certain conditions

I want to create a MySql query to insert into a table and when duplication happens, I want to check the existing record to determine whether to REPLACE or IGNORE. When a certain field of the existing record is 0 then do REPLACE, otherwise do IGNORE. How can this be done in a performant way?

Upvotes: 0

Views: 93

Answers (2)

Pepper
Pepper

Reputation: 587

Assuming your "check" column (the one that should be 0 for an update to happen) is c2, you could do something like that:

INSERT INTO table (id, c2, c3, c4)
VALUES
    (1, 'val2', 'val3', 'val4')
ON DUPLICATE KEY UPDATE
    c2 = IF(c2 = 0, VALUES(c2), c2),
    c3 = IF(c2 = 0, VALUES(c3), c3),
    c4 = IF(c2 = 0, VALUES(c4), c4);

It's basically an INSERT ... IGNORE if c2 != 0, and otherwise an INSERT ... UPDATE, which I think is what you want.

Of course you can adapt it to your needs, no need to update all the colums, or use the same check for each column

Upvotes: 1

jvk
jvk

Reputation: 2201

Create a unique composite index on number and name.

Alter table table1 Add unique index idx_unq(`number`,`name`);

Then do an Insert Ignore INTO table1(number,name) VALUES(num, name);

That should prevent duplicate from being inserted into the table.

useful link for http://www.sitepoint.com/use-unique-indexes-mysql-databases/

Upvotes: 0

Related Questions