Have Geek Brain

Mysql insert multiple lines, return lines that failed

When I try to insert (say) 30 rows into a table.

for instance

    INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
     ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   ), ... ;

And the cust_name must be unique. How can I then identify the records that were not inserted because their cust_name already exists?

Can I get them back?

+2


source to share


6 answers


Without the IGNORE clause tagged into your insert statement, I would expect the command to fail if you try to insert duplicates. With the IGNORE clause, the statement returns an information string giving the number of records, duplicates, and warnings.

For more details, please see

http://dev.mysql.com/doc/refman/5.1/en/insert.html



and find "Duplicates" on the page. This will take you to the place where it is explained in detail. However, information about which of the lines that failed cannot be retrieved this way, and I don't know of any other method to do this. The mysqlimport utility and LOAD DATA INFILE statement also do not have this option.

So if I really need to do this, I would write a script for it.

+4


source


You can use 3 different strategies for handling UNIQUE

index errors or PRIMARY KEY

when multiple inserts:

  • If you use the keyword IGNORE

    in your expression INSERT

    , errors that occur while executing the statement INSERT

    are treated as warnings and other lines are inserted.

  • If you specify a keyword ON DUPLICATE KEY UPDATE

    in your expression INSERT

    and a string is inserted that will result in a duplicate value in the index UNIQUE

    or PRIMARY KEY

    , the UPDATE

    old string is executed as you indicate in your statement, for example, you can add "(old)" to the previously inserted customer name in your special script.

  • If you use the keyword REPLACE

    instead INSERT

    , in the case of a duplicate value in the index UNIQUE

    or PRIMARY KEY

    , the previously inserted row will be deleted and the new row will be inserted instead.

read more on:



http://dev.mysql.com/doc/refman/5.1/en/insert.html

Good luck.

+4


source


I don’t know about your requirements, but a unique client name is NOT a GOOD concept

+1


source


as far as i know it is impossible.

If you are using a different language to insert your data, you can insert one row at once and create a loop. this way you can easily determine which lines failed.

0


source


If you are inserting multiple records into one query, you cannot easily find out which record failed to add. However, you can change this to 30 statements, each only inserting one record, and then if one of them fails, you instantly get what it was.

0


source


I'm not sure if you are using dummy data in there, but is there any reason why the cust_name should be unique, I can understand the id key or something, but there will surely be cases where the names conflict. The phone book is full of error records, is there a cust_id instead of a table that you could use to identify?

0


source






More articles:


All Articles