SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 confused how to this update,select, update delete
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
200 Posts

Posted - 11/06/2012 :  12:26:57  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

2143 Posts

Posted - 11/06/2012 :  13:08:33  Show Profile  Reply with Quote

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









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

Go to Top of Page

mike13
Posting Yak Master

Netherlands
200 Posts

Posted - 11/06/2012 :  13:22:31  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

India
52249 Posts

Posted - 11/06/2012 :  13:46:39  Show Profile  Reply with Quote
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

Netherlands
200 Posts

Posted - 11/06/2012 :  15:12:41  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

India
52249 Posts

Posted - 11/06/2012 :  15:15:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5911 Posts

Posted - 11/06/2012 :  15:24:16  Show Profile  Reply with Quote
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

Netherlands
200 Posts

Posted - 11/06/2012 :  17:21:39  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

India
52249 Posts

Posted - 11/07/2012 :  09:27:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5911 Posts

Posted - 11/07/2012 :  10:18:19  Show Profile  Reply with Quote
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

Netherlands
200 Posts

Posted - 11/07/2012 :  12:15:41  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
okay, but still do not know how to solve the existing duplicates problem?

thanks a lot.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5911 Posts

Posted - 11/07/2012 :  13:35:54  Show Profile  Reply with Quote
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

Edited by - TG on 11/07/2012 13:43:33
Go to Top of Page

mike13
Posting Yak Master

Netherlands
200 Posts

Posted - 11/08/2012 :  10:34:36  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi TG,

That did the trick thanks a lot :-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000