Mysql query for comparing two tables and another for updatin

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
aussie_clint
Forum Commoner
Posts: 41
Joined: Mon Jul 31, 2006 9:14 am
Location: Brisbane, Australia
Contact:

Mysql query for comparing two tables and another for updatin

Post 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
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Can you post the structure of the tables please?

Use the correct formatting tags so that it is readable.
aussie_clint
Forum Commoner
Posts: 41
Joined: Mon Jul 31, 2006 9:14 am
Location: Brisbane, Australia
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
Post Reply