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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 confused how to this update,select, update delete

Author  Topic 

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-06 : 12:26:57
Hi All,

I got a table

id- Orderid - Productid - optionid - quantity - unitcost

385937 265 454 454 1 2,00
385938 265 454 454 1 2,00
385939 266 600 430 1 8,50

and i got double records that have to be joined show it looks like this:

new 265 454 454 2 2,00
385939 266 600 430 1 8,50

so it adds up the records that have the same orderid, productid and optionid

Thanks 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 #yak
values(385937, 265, 454, 454, 1, 2.00)
,(385938, 265 ,454, 454 ,1, 2.00)
,(385939 ,266, 600, 430, 1, 8.50)

select * from #yak

select case when COUNT(*) > 1 then 'new' else max(id) end as ID, orderid,productid,optionid, SUM(quantity) as Quantity, Unitcost, COUNT(*)
from #yak
group by orderid,productid,optionid, Unitcost

drop table #yak
[/code]








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 1
what 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
Go to Top of Page

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 1
what 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 scenario
i would say delete one amd map any references it may have to other

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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
Go to Top of Page

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 point
If you have PK defined properly on columns then you definitely wont have duplicates present in your table
TG 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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.
Go to Top of Page

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 #yourTable
values(385937, 265, 454, 454, 1, 2.00)
,(385938, 265 ,454, 454 ,1, 2.00)
,(385939 ,266, 600, 430, 1, 8.50)

select * from #yourTable

select max(id) as maxid
,orderid
,productid
,optionid
,SUM(quantity) as Quantity
,Unitcost
into #staging
from #yourTable
group by orderid
,productid
,optionid
,Unitcost
having count(*) > 1

update y set
y.quantity = s.quantity
from #staging s
join #yourTable y on y.id = s.maxid

delete y
from #yourTable y
join #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.id

select * from #yourTable

drop table #yourTable
drop table #staging

EDIT:
here is the output:

id Orderid Productid optionid quantity unitcost
---------------------------- ----------- ----------- ----------- ---------------------
385937 265 454 454 1 2.00
385938 265 454 454 1 2.00
385939 266 600 430 1 8.50


id Orderid Productid optionid quantity unitcost
---------------------------- ----------- ----------- ----------- ---------------------
385938 265 454 454 2 2.00
385939 266 600 430 1 8.50


Be One with the Optimizer
TG
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-08 : 10:34:36
Hi TG,

That did the trick thanks a lot :-)
Go to Top of Page
   

- Advertisement -