MySQL Import & Replace via CSV

csvexportimportMySQLphpmyadmin

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.

Best Answer

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.

Related Solutions

Sql-server – Best way to re-import large amount of data with minimal downtime

A solution I've used in the past (and have recommended here and on StackOverflow before) is to create two additional schemas:

CREATE SCHEMA shadow AUTHORIZATION dbo;
CREATE SCHEMA cache  AUTHORIZATION dbo;

Now create a mimic of your table in the cache schema:

CREATE TABLE cache.IPLookup(...columns...);

Now when you are doing your switch operation:

TRUNCATE TABLE cache.IPLookup;
BULK INSERT cache.IPLookup FROM ...;

-- the nice thing about the above is that it doesn't really
-- matter if it takes one minute or ten - you're not messing
-- with a table that anyone is using, so you aren't going to
-- interfere with active users.


-- this is a metadata operation so extremely fast - it will wait
-- for existing locks to be released, but won't block new locks
-- for very long at all:

BEGIN TRANSACTION;
  ALTER SCHEMA shadow TRANSFER    dbo.IPLookup;
  ALTER SCHEMA dbo    TRANSFER  cache.IPLookup;
COMMIT TRANSACTION;


-- now let's move the shadow table back over to
-- the cache schema so it's ready for next load:

ALTER SCHEMA cache TRANSFER shadow.IPLookup;
TRUNCATE TABLE cache.IPLookup; 

-- truncate is optional - I usually keep the data
-- around for debugging, but that's probably not
-- necessary in this case.

This will be more cumbersome if you have foreign keys and other dependencies (since you may have to drop those and re-create them), and of course it completely invalidates statistics etc. and this, in turn, can affect plans, but if the most important thing is getting accurate data in front of your users with minimal interruption, this can be an approach to consider.

Mysql – How to change the display of an empty string in thesql command line tool

Bug #20067 empty string returns NULL as dispalyed value

This bug is possibly fixed 5.0.23. Look at the document referenced by the above page.

Related Question