Insert Data from Temporary Table to Main Table

[FONT=“Georgia”]Hello.

I’m working on a website backend for an online directory.

I’d like to allow website admins to add or update entries by simply uploading a tab-delimited text file (exported from Excel or Calc) because that would be the easiest option for them to work on, and manage such a large amount of data at once.

I’m working on the action page for the upload form now.

I’ve gotten as far as adding the data from the uploaded text file (or csv) to a temporary table, upon which I ran a few queries to clean the data. Perfect.

Now I’m trying to work out how best to import that new data from the temporary table to the main table.

I can loop through the records and indiscriminately insert everything, but that would allow duplicate entries to slip through, plus I’d like to allow new data to replace old data where it exists.

Is there a simple way to allow MySQL to do this kind of query all in one step, or would I need to insert everything, then run some further queries on it afterward to find and delete what I don’t need anymore?

[/FONT]

If you have a unique index on the fields that identify a ‘duplicate entry’, you can use the INSERT … ON DUPLICATE KEY UPDATE syntax.

If you don’t have it, create it… :wink:

My tables look something like this;

TEMPORARY TABLE

ex_category | ex_displayname | ex_safename | ex_address | ex_country | ex_phone | ex_id


MAIN TABLE

bus_id | bus_displayname | bus_safename | bus_address | bus_country | bus_phone

The ex_id and the bus_id are not the same, so hence my delimma.

ex_id is auto-incremented when the data is inserted from the upload file.

[FONT=“Georgia”]It’s used for running those cleaning queries mentioned above, though I suppose I could ditch it now if it helps the import into the main table.

Duplicate entries can be determined by comparing “safenames” + countries.

[/FONT]

So, create a unique index on bus_safename.

[FONT=“Georgia”]That is, if safename and country are the same, then it’s a duplicate, and the new can replace the old.

[/FONT]

[FONT=“Georgia”]Yes, sorry I updated my post.

I should have mentioned it before.

Looking at your link now.

[/FONT]

Ok, create the unique index on bus_safename+bus_country :slight_smile:

[FONT=“Georgia”]You can do that?

I thought only one field can be a unique index at a time.

Although I’m now thinking I could create a new field by merging the safename with the country name and using that as the index, “eg. joeskitchentrinidad”. It would mean recoding a few pages though.

Is that what you meant?

[/FONT]

no, a compound index

ALTER TABLE maintable
ADD UNIQUE ( bus_displayname , bus_country )

:slight_smile:

No, you can make indexes on multiple fields.

Edit: oops, I meant multiple columns of course… :smiley:

[FONT=“Georgia”]Okay, I’ll try it out.

That shouldn’t cause any conflicts with the bus_id , right? It’s the auto-incremented primary key.

The frontend of the website uses bus_id to look things up.

[/FONT]

[FONT=“Georgia”]Okay.

Backing up everything and let’s go again!

Thanks!

[/FONT]

Right.

bus_id is unique, check

and now, (bus_displayname,bus_country) is also unique, check

so the ON DUPLICATE KEY part will fire if ~any~ unique key is violated

but here’s the deal… why would you attempt to insert a row with the same bus_id as one you already have?

so to answer your question, no, the second unique key will not interfere

[FONT=“Georgia”]Wait no, then I will have a problem, which is that many records will carry the same country.

It needs to replace if both country and business name are the same.

That is, if bus_safename and bus_country are the same as ex_safename and ex_country , then it should replace.

[/FONT]

that’s not a problem

to see what i mean, ask yourself this – do you want your table to have only one row per country?

When Rudy says “~any~ unique key” he doesn’t mean business name, or country, or id.
There are only two unique keys: id and businessname+country

A compound key is considered in its entirety (is that correct english? ;)), not each single column alone.

I’m getting the following error in phpMyAdmin;

#1071 - Specified key was too long; max key length is 1000 bytes

[FONT=“Georgia”]Is Varchar (255) too long? What’s the max I’m allowed? 125 characters?

[/FONT]

[FONT=“Georgia”]125 it is.

I used 93 for safename and 32 for country and got through.

Trying out the table importing now and will let y’all know if it works out.

Thanks a lot for all the help.

[/FONT]

[FONT=“Georgia”]I’ve been having a hard time getting this working.

I wrote the following statement which does nothing (or seems to) and produces no error messages either;[/FONT]

// Insert
	$query = "INSERT
		INTO main_table (bus_displayname , bus_safename , bus_phone , bus_address01 , bus_address03 , bus_country , bus_datetime_rec , bus_status)
		SELECT ex_displayname , ex_safename , ex_phone , ex_address01 , ex_address03 , ex_country , ex_datetime_rec , ex_status FROM temporary_table
		ON DUPLICATE KEY UPDATE main_table bus_displayname = ex_displayname , bus_safename = ex_safename , bus_phone = ex_phone , (etc) ";

	mysql_query($query);

[FONT=“Georgia”]I’m reading something on IF NOT EXISTS now and thinking of trying a statement like that instead.

[/FONT]