CODEWITHSUNDEEP
mysqlinsertduplicates
eozzy
eozzy

Reputation: 68680

Inserting row while avoiding duplicated in MySQL

$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.

Upvotes: 0

Views: 79

Answers (1)

Barmar
Barmar

Reputation: 780984

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.

Upvotes: 3

Related Questions