comparing long lists in SQL

mysql

[belfasttim]

Hi all-- I'd appreciate any ideas of tips or tactics on this issue--

I have a DB table with about 10,000 rows, call it "products"-- I populate this table with a datafeed from the vendor. However, the vendor, in their wisdom, has declined to add discontinued items to the feed, so there's no real way for me to know what products should be "live" and which ones need to be delisted, short of comparing the latest feed with the products table and deleting items from products that are not in the feed.

I've thought of a few ways to attack this, from writing out the feed ID's into a temp table, to dumping the feed into a text file, but I'm curious if someone knows the best practice for this scenario.

Thanks!

[lammert]

Would temporarily marking all products in your database as "discontinued" and than setting them as "current" if it appears in the newest vendor datafeed be an option?

With use of a temporary table, this looks like:

Merge the new vendor datafeed with your current database, preventing double entries

Create a temporary table with two fields: ID and "discontinued".

Copy the unique ID of all products in your main database to the temporary table and set the discontinued field to TRUE.

Parse the vendor datafeed and set all "discontinued" fields to FALSE if they appear in the datafeed

Remove/mark all entries in your main product database which have the "discontinued" field still set in the temporary table.

[belfasttim]

thanks lammert! that's basically the exact method I settled on. I download the list, write it to a text file and use the load data infile to put it up to a temp table that basically just has the product ID and the # of photos the product has. Then I set all products in the main products table to inactive, then back to active if live_table.id = temp.id. Then I can query for any inactive products, and using the photos count, delete all photos from the images server.

Thanks for your response.