MySQL - ignore insert error: duplicate entry

Question

I am working in PHP.

Please what's the proper way of inserting new records into the DB, which has unique field. I am inserting lot of records in a batch and I just want the new ones to be inserted and I don't want any error for the duplicate entry.

Is there only way to first make a SELECT and to see if the entry is already there before the INSERT - and to INSERT only when SELECT returns no records? I hope not.

I would like to somehow tell MySQL to ignore these inserts without any error.

Thank you

insert ignore works for me The problem am having some rows are deleted from table and am trying to insert the rows same id which were deleted previously in that case "insert ignore" statement worked — Balaji
– Balaji, Commented Jun 6, 2017 at 3:56

zombat · Accepted Answer · 2009-05-01 18:09:17Z

198

You can use INSERT... IGNORE syntax if you want to take no action when there's a duplicate record.

You can use REPLACE INTO syntax if you want to overwrite an old record with a new one with the same key.

Or, you can use INSERT... ON DUPLICATE KEY UPDATE syntax if you want to perform an update to the record instead when you encounter a duplicate.

Edit: Thought I'd add some examples.

Examples

Say you have a table named tbl with two columns, id and value. There is one entry, id=1 and value=1. If you run the following statements:

REPLACE INTO tbl VALUES(1,50);

You still have one record, with id=1 value=50. Note that the whole record was DELETED first however, and then re-inserted. Then:

INSERT IGNORE INTO tbl VALUES (1,10);

The operation executes successfully, but nothing is inserted. You still have id=1 and value=50. Finally:

INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200;

You now have a single record with id=1 and value=200.

edited May 1, 2009 at 18:09

answered May 1, 2009 at 17:54

zombat

94.5k25 gold badges158 silver badges165 bronze badges

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

3 Comments

gradbot Over a year ago

"If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead." -from the above insert link

zombat Over a year ago

Warnings do not raise an error however, so your script will continue to execute. From the manual: If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

Chinoto Vokro Over a year ago

IGNORE will ignore errors that may have nothing to do with duplicate keys. It's better to do ON DUPLICATE KEY UPDATE field=field so you can catch those errors and as a bonus, not have any warnings.

gehsekky · Accepted Answer · 2019-08-13 23:37:42Z

-1

You can make sure that you do not insert duplicate information by using the EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

This statement inserts multiple records with a subselect.

If you wanted to insert a single record, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

from http://www.techonthenet.com/sql/insert.php

edited Aug 13, 2019 at 23:37

user3956566

answered May 1, 2009 at 17:52

gehsekky

3,2192 gold badges23 silver badges14 bronze badges

2 Comments

womp Over a year ago

This is not the proper way to do this. This solution is slow, harder to maintain and prone to errors. Using INSERT with IGNORE would be a much better solution.

user2607743 Over a year ago

This appears to be a Cartesian Join, which is not advised in this circumstance.

Kirtan · Accepted Answer · 2009-05-01 17:50:18Z

-2

You can use triggers.

Also check this introduction guide to triggers.

answered May 1, 2009 at 17:50

Kirtan

21.7k6 gold badges50 silver badges61 bronze badges

Comments

Jordan S. Jones · Accepted Answer · 2009-05-01 17:51:01Z

-3

Try creating a duplicate table, preferably a temporary table, without the unique constraint and do your bulk load into that table. Then select only the unique (DISTINCT) items from the temporary table and insert into the target table.

answered May 1, 2009 at 17:51

Jordan S. Jones

13.9k5 gold badges47 silver badges50 bronze badges

Comments

chandrasekar · Accepted Answer · 2015-11-25 06:37:27Z

$duplicate_query=mysql_query("SELECT * FROM student") or die(mysql_error());
$duplicate=mysql_num_rows($duplicate_query);
if($duplicate==0)
{
    while($value=mysql_fetch_array($duplicate_query)
    {
        if(($value['name']==$name)&& ($value['email']==$email)&& ($value['mobile']==$mobile)&& ($value['resume']==$resume))
        {
            echo $query="INSERT INTO student(name,email,mobile,resume)VALUES('$name','$email','$mobile','$resume')";
            $res=mysql_query($query);
            if($query)
            {
                echo "Success";
            }
            else
            {
                echo "Error";
            }
            else
            {
                echo "Duplicate Entry";
            }
        }
    }
}
else
{
    echo "Records Already Exixts";
}

Collectives™ on Stack Overflow

MySQL - ignore insert error: duplicate entry

5 Answers 5

Examples

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Examples

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related