MySQL INSERT if one of primary key value doesn't match

Question

I would like to do MySQL query like this:


if((value1 != dbPrimaryValue1) OR (value2 != dbPrimaryValue2))
      INSERT ROW
else
      DO NOTHING

Lets try example:


CREATE TABLE `tmp` (
    `one` int NOT NULL,
    `two` int NOT NULL,
    `three` int NOT NULL);

ALTER TABLE `tmp`
ADD PRIMARY KEY (`one`, `two`);

INSERT INTO `tmp`
    (`one`, `two`, `three`)
    VALUES (1,2,3);

INSERT INTO `tmp`
    (`one`,`two`,`three`) 
    VALUES (10,20,30),
           (1,999,999),
       (999,2,999),
       (1,2,999)
    ON DUPLICATE KEY 
           UPDATE `one` = `one`; // or some dummy no-source-drain operation

Result is here:


select * from tmp;
+-----+-----+-------+
| one | two | three |
+-----+-----+-------+
|   1 |   2 |     3 |
|  10 |  20 |    30 |
|   1 | 999 |   999 |
| 999 |   2 |   999 |
+-----+-----+-------+

U would like to have result like this:


 select * from tmp;
+-----+-----+-------+
| one | two | three |
+-----+-----+-------+
|   1 |   2 |     3 |
|  10 |  20 |    30 |
+-----+-----+-------+

Is it possible to make this query? I'm operating with huge data and procedure like load -> compare -> save is not possible. THANKS!

Riimu · Accepted Answer · 2011-03-24 09:22:51Z

1

Simply make both fields unique separately. For example:

CREATE TABLE `tmp` (
    `one` int NOT NULL UNIQUE,
    `two` int NOT NULL UNIQUE,
    `three` int NOT NULL);

Or add the constraints via:

ALTER TABLE `tmp` ADD UNIQUE (`one`);
ALTER TABLE `tmp` ADD UNIQUE (`two`);

answered Mar 24, 2011 at 9:22

Riimu

1,4278 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steven Ryssaert · Accepted Answer · 2011-03-24 09:23:16Z

0

If you create a UNIQUE key constraint, the database will not allow you to insert them automatically.

From MySQL forum:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

answered Mar 24, 2011 at 9:23

Steven Ryssaert

1,97715 silver badges28 bronze badges

Collectives™ on Stack Overflow

MySQL INSERT if one of primary key value doesn't match

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related