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 |
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-06 : 12:26:57
|
Hi All,I got a tableid- Orderid - Productid - optionid - quantity - unitcost385937 265 454 454 1 2,00385938 265 454 454 1 2,00385939 266 600 430 1 8,50and i got double records that have to be joined show it looks like this:new 265 454 454 2 2,00385939 266 600 430 1 8,50so it adds up the records that have the same orderid, productid and optionidThanks a lot |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-11-06 : 13:08:33
|
[code]create table #yak (id varchar(100), Orderid int, Productid int, optionid int, quantity int, unitcost money)insert into #yakvalues(385937, 265, 454, 454, 1, 2.00),(385938, 265 ,454, 454 ,1, 2.00),(385939 ,266, 600, 430, 1, 8.50)select * from #yakselect case when COUNT(*) > 1 then 'new' else max(id) end as ID, orderid,productid,optionid, SUM(quantity) as Quantity, Unitcost, COUNT(*)from #yakgroup by orderid,productid,optionid, Unitcostdrop table #yak[/code]How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-06 : 13:22:31
|
Thanks,But maybe i didn't myself clear.I do not want to display the records, i want to update them and delete the duplicated one.thanks a lot |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-06 : 13:46:39
|
quote: Originally posted by mike13 Thanks,But maybe i didn't myself clear.I do not want to display the records, i want to update them and delete the duplicated one.thanks a lot
convert the select to an update or delete accordingly------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-06 : 15:12:41
|
mmm that is the question.i got 2 records, that should turn into 1what is best, delete both and enter a new one?or update one and delete the other?like the first one better, how do i do that in sql statement please thanks a lot |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-06 : 15:15:00
|
quote: Originally posted by mike13 mmm that is the question.i got 2 records, that should turn into 1what is best, delete both and enter a new one?or update one and delete the other?like the first one better, how do i do that in sql statement please thanks a lot
depends on scenarioi would say delete one amd map any references it may have to other------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-06 : 15:24:16
|
While you're fixing the existing data you should consider permanently solving the problem by putting the appropriate constraints on the table. You should have a Primary Key and/or unique constraint to prevent these duplicates.Be One with the OptimizerTG |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-06 : 17:21:39
|
hi,i got a primary key, id field.But that is not answer to my question.If i know to do it, i will run the SP everytime a customer checksout.so it never happens again |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-07 : 09:27:39
|
quote: Originally posted by mike13 hi,i got a primary key, id field.But that is not answer to my question.If i know to do it, i will run the SP everytime a customer checksout.so it never happens again
Nope...I dont think you got TG's pointIf you have PK defined properly on columns then you definitely wont have duplicates present in your tableTG was suggesting to redefine your PK so that it matches your business requirement and you wont be left with any duplicates in that case.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-07 : 10:18:19
|
Thanks Visakh. The other point I was making is:Sometimes you need to enforce uniqueness across columns other than the primary key. In that case you can specify an "alternate key" or "logical key" by creating a unique constraint on those columns. That is often the case when your primary key is a "surrogate key" like for instance an identity column or a uniqueidentifier.Be One with the OptimizerTG |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-07 : 12:15:41
|
okay, but still do not know how to solve the existing duplicates problem?thanks a lot. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-07 : 13:35:54
|
Try this:create table #yourTable (id varchar(100), Orderid int, Productid int, optionid int, quantity int, unitcost money)insert into #yourTablevalues(385937, 265, 454, 454, 1, 2.00),(385938, 265 ,454, 454 ,1, 2.00),(385939 ,266, 600, 430, 1, 8.50)select * from #yourTableselect max(id) as maxid ,orderid ,productid ,optionid ,SUM(quantity) as Quantity ,Unitcostinto #stagingfrom #yourTablegroup by orderid ,productid ,optionid ,Unitcosthaving count(*) > 1update y set y.quantity = s.quantityfrom #staging sjoin #yourTable y on y.id = s.maxiddelete yfrom #yourTable yjoin #staging s on s.orderid = y.orderid and s.productid = y.productid and s.optionid = y.optionid and s.unitcost = y.unitcost and s.maxid > y.idselect * from #yourTabledrop table #yourTabledrop table #staging EDIT:here is the output:id Orderid Productid optionid quantity unitcost---------------------------- ----------- ----------- ----------- ---------------------385937 265 454 454 1 2.00385938 265 454 454 1 2.00385939 266 600 430 1 8.50id Orderid Productid optionid quantity unitcost---------------------------- ----------- ----------- ----------- ---------------------385938 265 454 454 2 2.00385939 266 600 430 1 8.50 Be One with the OptimizerTG |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-08 : 10:34:36
|
Hi TG,That did the trick thanks a lot :-) |
|
|
|
|
|
|
|