homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

How to merge two tables efficiently in MySQL

10+ Year Member

Msg#: 3955368 posted 6:29 pm on Jul 19, 2009 (gmt 0)


I need your help in figuring out how to merge two tables -

1) Both table columns are identical.
2) First table is the master production table
3) Second table is the temp table that sucks data from various feeds nightly.

What I want to do -
1) Lookup if row is already there in master table (determined by product SKU - already indexed),

1 a) If there, update data e.g. availability column, price column.
1 b) If not there, insert record into master table.

2) If record is not in temp table and is there is master table, mark the status column as 'inactive'

I have written a program that loops over each row of temp table and does the above, problem is that it takes 1 hr every 10,000 rows and I have ~ 1Million.

Is there a more efficient way to do this?

Please help.




WebmasterWorld Senior Member vincevincevince us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 3955368 posted 4:41 am on Jul 21, 2009 (gmt 0)

This does the first part (insert missing from master, and update if exists):
INSERT INTO `master` SELECT * FROM `temp` ON DUPLICATE KEY UPDATE `master`.`field1` = `temp`.`field1`, `master`.`field2` = `temp`.`field2`

Secondly do something like:
UPDATE `master` SET `status` = 'inactive' WHERE `sku` NOT IN (SELECT `sku` FROM `temp`)

Both are untested but should give you a good general direction to doing this without having to create a massive loop.


10+ Year Member

Msg#: 3955368 posted 6:39 pm on Jul 23, 2009 (gmt 0)

Thanks, I will try this and post performance gains.

I did add indexes on multiple items and that helped speed up the lookup operation and reduced 1 hr every 10K rows to more like 10 min.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved