Detect if MySQL has duplicates when inserting

Question

I have a table with the columns value1, value2 and value3.

Every few months, all rows will need to change their 'value1' to a different value. So far I have the following code and I cannot figure out for the life of me why it is not working. Instead of only modifying column one, it generates an entire new row of information.

Thanks in advance.

INSERT INTO table (value1, value2, value3)
            VALUES ('$valueForValue1', '$valueForValue2','$valueForValue3')
            ON DUPLICATE KEY UPDATE
                `value1`='$valueForValue1',
                `value2`='$valueForValue2',
                `value3`='$valueForValue3',

What indices/constraints do you have on the table? Besides ...all rows will need to change their 'value1' to a different value... looks like a job for UPDATE statement rather than INSERT if you really meant all rows. Can you elaborate a little bit more on what are you trying to achieve? — peterm
– peterm, Commented Nov 9, 2013 at 5:09
Forgive me, I am very new to MySQL. What do you mean indices/constraints? I haven't set any, if that helps. How would I go about implementing that? — Will
– Will, Commented Nov 9, 2013 at 5:20
I need value2 and value3 to stay the same, but value1 should be updated every few months. — Will
– Will, Commented Nov 9, 2013 at 5:20

peterm · Accepted Answer · 2013-11-09 20:22:55Z

1

In order to be able to change a value of value1 with ON DUPLICATE KEY clause you have to have either a UNIQUE constraint or a PRIMARY KEY on (value2, value3).

ALTER TABLE table1 ADD UNIQUE (value2, value3);

Now to simplify your insert statement you can also use VALUES() in ON DUPLICATE KEY like this

INSERT INTO Table1 (`value1`, `value2`, `value3`)
VALUES ('$valueForValue1', '$valueForValue2', '$valueForValue3')
ON DUPLICATE KEY UPDATE value1 = VALUES(value1);

Here is SQLFIddle demo

answered Nov 9, 2013 at 20:22

peterm

93k16 gold badges157 silver badges162 bronze badges

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

1 Comment

Will Over a year ago

Worked perfectly! Thank you so much!

spencer7593 · Accepted Answer · 2013-11-09 05:22:00Z

1

The UPDATE action of the ON DUPLICATE KEY clause will only be executed if the row being inserted would cause the violation of a UNIQUE constraint. That means there needs to be a primary key or a unique index on the table.

If you want to modify existing rows, you'd really want to use an UPDATE statement.

To change the value in a column of existing rows, replacing 'oldvalue' with 'newvalue', you could do something like this:

UPDATE mytable
   SET col1 = 'newvalue'
 WHERE col1 = 'oldvalue'

answered Nov 9, 2013 at 5:22

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Collectives™ on Stack Overflow

Detect if MySQL has duplicates when inserting

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related