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

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

---

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

---

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

---

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

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