reloading data files into a database

yahyac

Hey peoples,

Question please. I still have not got my site up and running with php/mysql yet, as we are simply waiting for the data to be "copied and pasted" into excel so we can load it to the database....but we're close.

Anyway, my question is regarding reloading data files into the database. for example, after I create the database for the FIRST TIME, i will load the data into the DB by using the following command (an example):

load data infile './products.txt' into table products
	fields terminated by '\t'
	optionally enclosed by '"'
	lines terminated by '\r\n';
load data infile './prices.txt' into table products_pricing
	fields terminated by '\t'
	optionally enclosed by '"'
	lines terminated by '\r\n';

Now, perhaps my method of updating the database is wrong, but my intentions were to have someone simple keep an updated excel file of the products/pricing (thousands of items), and as updates are made to this file, i was hoping to be able to "reload" the aforementioned files into the database, and IF anything was changed, it would re-write that particular tuple. So, for example, if the price on Item #BLAH changed, then if i reload, BLAH's price would change accordingly.

So, first off, is this possible? Is there some type of "load data infile" parameters that I can use to instruct the DB to ignore any and all tuples that are 100% identical, AND also if it finds any tuples with any changes at all, to load the changes?

If not, are there any suggestions? Ideally, i wanted to keep the "real" copy of the DB on my machine, and update the live DB as needed. Thus, if anything ever went wrong, my copy is still here and working. And there are other logical reasons for this also.

Please advise.

Thanks.
Jonathan

yahyac

I should have stated that I have read about REPLACE but am unsure how to use this with the load data infile command.

Also, I am unsure how this works with constraints and INNODB tables.

For example, here's my products table:

CREATE TABLE products (
	item_num VARCHAR(20) NOT NULL,
	supplier_num VARCHAR(10) NOT NULL, 
	name VARCHAR(100),
	description VARCHAR(1000),
	size VARCHAR(255),
	color VARCHAR(255),
	logo_method VARCHAR(100),
	logo_loc_size VARCHAR(100),
	turnaround VARCHAR(255),
	addl_notes VARCHAR(255),
	setup DECIMAL(6,2),
	catID VARCHAR(20) NOT NULL,
	index (catID),
	subcatID VARCHAR(20) NOT NULL,
	index (subcatID),
	PRIMARY KEY (item_num),
	index (supplier_num),
	FOREIGN KEY (supplier_num) REFERENCES suppliers (supplier_num)
	ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (catID) REFERENCES category (catID)
	ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (subcatID) REFERENCES category (subcatID)
	ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;

Other tables reference the above products table and have constraints linking to that table. So if I have and ORDER that references a product in the above table, and if i reload the data using the REPLACE command, which essentially DELETES and then INSERTS, I should assume that it won't let me do this right? It will probably try to delete and then say that it cannot because a "foreign key constraint fails, blah, blah, etc." .?.

So first question is regarding the syntax with using REPLACE with the load data infile command.

Second question is will this work with INNODB tables anyway.

Thanks.
Jonathan

yahyac

Anyone?

Thought this would have been a simple question.