Best way to compare and delete or insert within tables??

[estafford]

I am working on a project and i'm not sure of the best way to do what I need to.

I have a CSV file that contains addresses.
I need to compare the csv file to a MySQL table (tbl_bl).

Then if the addresses are not found in tbl_bl, I need to add the addresses to another table (tbl_wl) Only if they do not already exist in the tbl_wl table.

I can create simple SQL queries but I'm not so sure about something this complex.

Can anyone give me an idea of the best way to accomplish this?

Thanks!!

 

[TonyGroves]

You could try the following:

(1) Backup your tables.
(2) Lock the tables.[tt]
LOCK TABLE tbl_bl WRITE, tbl_wl WRITE;[/tt]
(3) Create a temporary field in tbl_bl and set it to 1; this will serve as a flag to indicate already-existing addresses.[tt]
ALTER TABLE tbl_bl ADD T TINYINT;
UPDATE tbl_bl SET T=1;[/tt]
(4) Add the CSV data to the table, specifying that existing addresses should not be overwritten. The records for non-existing address will be added, and their T field set to null.[tt]
LOAD DATA INFILE 'dir/file.csv' IGNORE INTO TABLE tbl_bl;[/tt]
This syntax assumes that your file has tab-separated fields, and that its field sequence is identical to the table's, but other options are possible.
(5) Create a temporary field in tbl_wl corresponding to that in tbl_bl.[tt]
ALTER TABLE tbl_wl ADD T TINYINT;[/tt]
(6) Copy the new addresses into tbl_wl, specifying that existing addresses should not be overwritten.[tt]
INSERT IGNORE tbl_wl SELECT * FROM tbl_bl WHERE T IS NULL;[/tt]
(7) Drop the temporary fields and unlock the tables.[tt]
ALTER TABLE tbl_bl DROP T;
ALTER TABLE tbl_wl DROP T;
UNLOCK TABLE;[/tt]

This method assumes that addresses are identified by unique keys, and the structures of the two tables are identical. It's a bit long-winded, but maybe somebody else will come up with a better way.

 

[estafford]

Thanks for the info.
The LOAD DATA feature is exactly what I needed.

Thanks