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
Mysql query for comparing two tables and another for updatin
Moderator: General Moderators
-
aussie_clint
- Forum Commoner
- Posts: 41
- Joined: Mon Jul 31, 2006 9:14 am
- Location: Brisbane, Australia
- Contact:
-
aussie_clint
- Forum Commoner
- Posts: 41
- Joined: Mon Jul 31, 2006 9:14 am
- Location: Brisbane, Australia
- Contact:
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
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
You can do an inner join on the tables where the two prices are different for the same part number:
this assumes that PartNumber is the primary key in both tables.
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)