CODEWITHSUNDEEP
mysqlsqlinsertduplicates
Will
Will

Reputation: 147

Detect if MySQL has duplicates when inserting

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',

Upvotes: 0

Views: 144

Answers (2)

peterm
peterm

Reputation: 92785

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

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

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'

Upvotes: 1

Related Questions