Alex Rivera | Logout

MySQL - ignore insert error: duplicate entry

Asked 2009-05-01T17:48:05.973
91

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

php mysql insert
Edit
Report

1 Answer

-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

answered 2009-05-01T17:52:10.753

Your Answer