Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
blindnz
Starting Member
3 Posts |
 Posted - 2011-02-22 : 19:40:11
|
| Hi,I have data in a DB which is a monthly snapshot of my stock levels.The issue I have is that we have had instances where the same stock item has been loaded twice, as in they have different product ID's but the supplier_product_code and supplier_number are identical.What I would like to do is merge these time series. So I have say product 14 and 15 made into product 15 with the timeseries data from 14 and 15.The database table I have had the columns, ProductID, Supplier_number and Supplier_product_code. I would like to create a query in SQL that finds products with the same supplier_number and Supplier_product_code and then overwrites the earlier productID with the newer one (max ProductID) and then i would have a complete timeseries for the product |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-22 : 23:45:07
|
| would be even better if you could explain the same with example e.g. sample rows for the table and the desired format of the same rows. CheersMIK |
 |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-02-23 : 03:38:54
|
You can use max and group by to identify productIds that should stay. Use having count(*) > 1 to identify only products that need to be merged. Then join subquery/derived table with original one on supplier_number and product code as a part of update with join. Restrict updates to rows where toUpdate.productId < toStay.productId I hope it helps.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-02-23 : 07:30:27
|
Sorry - 1 thing:quote:
The database table I have had the columns, ProductID, Supplier_number and Supplier_product_code. I would like to create a query in SQL that finds products with the same supplier_number and Supplier_product_code and then overwrites the earlier productID with the newer one (max ProductID) and then i would have a complete timeseries for the product
Why not just DELETE the earlier ones? Doesn't that end up nicer -- you no longer have duplicate rows. Also -- If I understand correctly these duplicates are unwanted and should not be possible from business rules?In that case there should be a constraint on the table -- this will highlight the problem code that is inserting duplicates (it will break and then you will fix it).Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
Clean DB data with SQL