MySQL INSERT into new table, SELECT from old table, UPDATE old table with new key

iceomnia

Okay, here goes!

INSERT INTO retailers
	(retailername, retailerdesc, retailerwebsite, retailerurl, active)
SELECT
	datasource_retailername,
	datasource_retailerdesc,
	datasource_retailerwebsite,
	datasource_retailertrackurl,
	1
FROM
	datasources_retailers_idx AS i
LEFT OUTER JOIN
	retailers AS r
ON
	r.retailername NOT REGEXP REPLACE(i.datasource_retailername, ' ','.+')

UPDATE datasources_retailers_idx SET idretailers=last_insert_id();

Here's what I'm trying to do:

Select from old table
check if there is a matching retailer in the new table
If not, insert retailers details into new table
Update a reference column in the old table with the INSERTID primary key value of that row from the new table

The problem is that - as it should - the 'last_insert_id' is the VERY LAST row entered into the database. - So every row in the old table is updated with the LAST inserted ID.

I am trying to avoid PHP completely and do it totally in mysql - I can do it in php, but it takes 9 hours to complete.

Someone please help!!!!

iceomnia

Any ideas - one of you geniuses must have some idea?

iceomnia

This is what someone else has posted on mySQL.com's forums:

Well, is the retailer name unique? If so, use it to get the id...

Get the new rows in place, assigning ids as it goes:

INSERT INTO ... SELECT ... LEFT JOIN ... WHERE ... IS NULL

Copy over the ids using multi-table UPDATE (see docs)

UPDATE a,b
SET a.id = b.id
WHERE a.name = b.name
AND a.id IS NULL

Maybe it is not quite what you need; hope it will give you some ideas.

So I tried this:

UPDATE
	datasources_retailers_idx  AS d
INNER JOIN
	retailers AS r
ON
	r.retailername     = d.datasource_retailername
   AND r.retailerdesc     = d.datasource_retailerdesc    

   AND r.retailerwebsite  = d.datasource_retailerwebsite 
   AND r.retailerurl      = d.datasource_retailertrackurl
SET
	d.idretailers = r.idretailers

...getting absolutely no-where...

Can anyone help?