[Resolved] compare records in two MySQL tables

[perpetualshaun]

I'm building an import routine for products on an eCom project.

Once the user uploads the file, it writes all the data to the import_products table. In the end, my client will be uploading THOUSANDS of records. Is there a MySQL query I could use to compare values in the import_products table to the values in the products table?

The field names are the same in both tables. I figure it will be quicker to compare and then delete from the import_products table for each record instead of updating the record in the products table every time. I know how I could do this comparison w/ PHP, but I thought that maybe someone knows of a way I could do it in MySQL which may be easier and faster.

Thanx,

Shaun

[AstroTeg]

Too many variables involved to give you a direct answer. How many fields are you looking to compare? How are you comparing those fields? What is the definition of a duplicate and what is not a duplicate?

If you just go off of product names looking for duplicates on exact matches, you could do two things. You could use the UNION operation and combine all like product names together to create a new list. But union only combines the same fields. So if you toss in everything, including a datetime stamp, union won't work the way you want it to.

Another option is to join the like fields together. By product name, left join or inner join depending on which direction you're trying to go in. For example, you might left join your new table with your existing table and find existing product names that join up as null. This means they do not exist in your existing table and that is a product you need to bring in to the existing table.

But then you probably get into issues about price and other attributes.

If your data structure is supper simple, I think one of the above methods would work great. But I'm not sure what you have involved so I'm thinking you'll find the PHP route to be more flexible in terms of finding rows that match and don't match.