MySQL Import & Replace via CSV

Question

I have a MySQL database table named items with 10,000+ records. Of these records, I would like to export only records where item_isbn IS NULL.

SELECT * FROM Items WHERE item_isbn IS NULL produces a table with 500 records, a sample of the data is below;

+---------+------------+-----------+
| item_id | item_title | item_isbn |
+---------+------------+-----------+
| 1 | Jo | NULL |
+---------+------------+-----------+
| 24 | Cars | NULL |
+---------+------------+-----------+
| 315 | Fun | NULL |
+---------+------------+-----------+
| etc | etc | etc |
+---------+------------+-----------+

I have exported this table as a CSV file, and have populated it with the necessary ISBNs via MS Excel. I'm now ready to re-import the data into the itens table.

How can I re-import these 500 records into the items table, whilst overwriting only the same 500 records, and maintaining the same item_id.

Is there a more efficient way of doing this?

I am using PhpMyAdmin.

Any help appreciated.

Answer 1

Simply delete the rows where item_isbn IS NULL and then import the updated CSV file.

While importing, you can disable auto_increment as well in case you're concerned about messing item_id identity.

Answer 2

Imported those records with new temp table and then, write update query based on item_id. this way your item_id is remains same and those records will be updated.

UPDATE items i1 INNER JOIN temp_items i2 
ON i1.item_id = i2.item_id 
SET i1.item_title =i2.item_title AND 
i1.item_isbn= i2.item_isbn;