CODEWITHSUNDEEP
mysqlsql
tempy
tempy

Reputation: 907

Check if a record with the same unique ID exists in SQL table before insert

How can I check if there's another record in the table with the same unique ID (the column name is ID), then and if one exists not do anything, otherwise insert the record?

Do I need to index the ID column and set it as unique/primary key?

Upvotes: 2

Views: 2781

Answers (3)

chue x
chue x

Reputation: 18803

One option is to use INSERT IGNORE. As the name implies it will INSERT rows that do not exist, and it will do nothing with duplicate rows.

CREATE TABLE Table1 (
`id` int NOT NULL PRIMARY KEY, 
`foo` varchar(20) 
);

INSERT IGNORE INTO Table1
    (`id`, `foo`)
VALUES
    (1, 'a'),
    (2, 'a'),
    (3, 'a'),
    (4, 'a'),
    (1, 'a'),
    (2, 'a'),
    (1, 'a');

The above will only insert the first 4 rows. The final 3 rows are not inserted because they have duplicate keys.

SQL Fiddle Demo

INSERT IGNORE requires that you have either a primary key or unique index.

More info on INSERT IGNORE (as well as other ways to handle duplicates) here: MySQL Handling Duplicates

Upvotes: 3

jdepypere
jdepypere

Reputation: 3553

In case you want to change it to also do something when the id alreadt exists, here's a snippet:

INSERT INTO table (key1, ...) VALUES (value1, ...) ON DUPLICATE KEY UPDATE dosomethingelse

So if the key already exists it will update whatever is after on duplicate key, else it will insert whatever is before that.

Upvotes: 1

kirilloid
kirilloid

Reputation: 14304

You may use REPLACE as INSERT OR UPDATE for that

Upvotes: 1

Related Questions