Inserting row while avoiding duplicated in MySQL

Question

$sql = "INSERT IGNORE INTO product (reference, description, price, created_at, updated_at)
VALUES ('{$sku}', '{$description}', '{$price}', '{$created_at}', '{$updated_at}')";

.. keeps on creating new rows because of the incrementing id column, how do I make SKU unique so it checks if SKU value exists, then UPDATE instead of INSERT.

Add a unique index on the reference column with ALTER TABLE or CREATE INDEX. — Barmar
– Barmar, Commented Jan 23, 2015 at 21:10
You may be able to use INSERT..ON DUPLICATE KEY. The SQL equivalent would be MERGE but that does not seem to be supported within MySQL. — Anthony Forloney
– Anthony Forloney, Commented Jan 23, 2015 at 21:10
@AnthonyForloney If he doesn't have a unique index, ON DUPLICATE KEY won't do anything. — Barmar
– Barmar, Commented Jan 23, 2015 at 21:11
Yes, I'm reading it but don't quite understand how to use. Just learning MySQL. :( — eozzy
– eozzy, Commented Jan 23, 2015 at 21:11
@Ismaestro All those answers assume you already have a unique key. They don't say how to create the unique key in the first place. — Barmar
– Barmar, Commented Jan 23, 2015 at 21:14

Barmar · Accepted Answer · 2015-01-23 21:17:53Z

3

Add a unique key on the column you don't want to allow duplicates of:

ALTER TABLE product ADD UNIQUE KEY (reference);

Once you've done this, you can use the ON DUPLICATE KEY UPDATE clause to update the row instead of ignoring the INSERT.

INSERT INTO product (reference, description, price, created_at, updated_at)
VALUES ('{$sku}', '{$description}', '{$price}', '{$created_at}', '{$updated_at}')
ON DUPLICATE KEY UPDATE 
    description = VALUES(description),
    price = VALUES(price),
    created_at = VALUES(created_at),
    updated_at = VALUES(updated_at)

Using VALUES in the ON DUPLICATE KEY clause picks up the values that would have been assigned during an INSERT of a new row.

edited Jan 23, 2015 at 21:17

answered Jan 23, 2015 at 21:12

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

5 Comments

eozzy Over a year ago

This is done just once? or every time I make a query?

Barmar Over a year ago

Just once. Indexes are part of the schema.

Marcus Adams Over a year ago

You need the unique key plus INSERT ... ON DUPLICATE KEY UPDATE.

eozzy Over a year ago

Strange issue. Although the $conn->query($sql) === TRUE) passes and it reports row created successfully but I don't see many entries in the DB. One for example is 0213A5 which isn't in the DB. I'm also mysqli_real_escape_string the strings incase that's causing the error.

eozzy Over a year ago

Figured it. There was a warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given so I changed it to $description_safe = mysqli_real_escape_string($conn, $description);

Collectives™ on Stack Overflow

Inserting row while avoiding duplicated in MySQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related