Page 1 of 1

Mysql query for comparing two tables and another for updatin

Posted: Thu Aug 03, 2006 7:04 am
by aussie_clint
Hi

I have a mysql database for a price list from my supplier and I’m wanting a query to compare the current price list (table) to the new one, for new products

And also another one to update the prices

The current price list has a few new columns but the first columns is the same (part_number)

Can some one help me as I’m fairly new to mysql

Thanks
Clint

Posted: Thu Aug 03, 2006 7:35 am
by GM
Can you post the structure of the tables please?

Use the correct formatting tags so that it is readable.

Posted: Thu Aug 03, 2006 7:54 am
by aussie_clint
first table


Prices

PartNumber varchar(50)
Description varchar(150)
CategoyName varchar(20)
CategoryID int(10)
ExTax decimal(7,2)
IncTax decimal(7,2)
MftrWebSite(50)

Second table

Prices_New

PartNumber varchar(50)
Description varchar(150)
CategoyName varchar(20)
CategoryID int(10)
ExTax decimal(7,2)
IncTax decimal(7,2)


Description is deferent as I have reworded things differently

Posted: Thu Aug 03, 2006 8:06 am
by feyd
Check Useful Posts. There's a thread linked detailing a query to find the difference between two tables. It may be of use.

Posted: Thu Aug 03, 2006 8:56 am
by GM
You can do an inner join on the tables where the two prices are different for the same part number:

Code: Select all

SELECT o.PartNumber, o.ExTax, n.ExTax, o.IncTax, n.IncTax, o.MftrWebSite
FROM 
prices o 
    INNER JOIN 
prices_new n 
    ON o.PartNumber = n.PartNumber 
    AND (o.ExTax <> n.ExTax OR o.IncTax <> n.IncTax)
this assumes that PartNumber is the primary key in both tables.