cancell duplicate orders

Hello Experts

I have a situation in the CustOrder table, for some reason I have noticed they were duplicate orders have been recorded,
the job which runs every wednesday considered it as new orders and submitted them to the broker to process.

I have loads of orders which I want to set the status from "2 Shared Ordered" to "Cancelled" for all those duplicate records.

in the sample below if you please see for "adam collins" they were 3 orders captured by the system all at "2008-09-10 07:40:03"

They have been sent to the broker for 3 times,

first at "2008-10-29 02:20:02" and then "05/11" and "12/11"

All I wanted to now is to keep the first record as it and set the status="Cancelled" for the other 2.

Please can someone advise me the SQL query for this?

Regards
Kay
learningnetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

learningnetAuthor Commented:

Status		OrderID	RFname		Shareid	BrokerSent		DateOrder
===========================================================================================
2 Share Ordered	161308	Adam Arnold	303	2008-10-29 02:20:03	2008-10-20 10:37:40
2 Share Ordered	161500	Adam Collins	305	2008-10-29 02:20:02	2008-09-10 07:40:03
2 Share Ordered	161698	Adam Collins	305	2008-11-05 02:20:03	2008-09-10 07:40:03
2 Share Ordered	161946	Adam Collins	305	2008-11-12 02:20:02	2008-09-10 07:40:03
2 Share Ordered	161693	adam sherief	305	2008-11-05 02:20:03	2008-10-02 20:30:40
2 Share Ordered	161913	adam sherief	305	2008-11-12 02:20:03	2008-10-02 20:30:40
2 Share Ordered	161464	adam sherief	305	2008-10-29 02:20:03	2008-10-02 20:30:40
2 Share Ordered	138136	ADRIAN  MORRIS	252	2005-11-09 04:00:17	2005-11-05 10:24:30
2 Share Ordered	161564	Adrian Brindley	264	2008-11-05 02:20:05	2008-10-02 15:32:23
2 Share Ordered	161352	Adrian Brindley	264	2008-10-29 02:20:05	2008-10-02 15:32:23
 
 
Desired 
 
Status		OrderID	RFname		Shareid	BrokerSent		DateOrder
===========================================================================================
2 Share Ordered	161308	Adam Arnold	303	2008-10-29 02:20:03	2008-10-20 10:37:40
2 Share Ordered	161500	Adam Collins	305	2008-10-29 02:20:02	2008-09-10 07:40:03
Cancelled	161698	Adam Collins	305	2008-11-05 02:20:03	2008-09-10 07:40:03
Cancelled	161946	Adam Collins	305	2008-11-12 02:20:02	2008-09-10 07:40:03
2 Share Ordered	161693	adam sherief	305	2008-11-05 02:20:03	2008-10-02 20:30:40
Cancelled	161913	adam sherief	305	2008-11-12 02:20:03	2008-10-02 20:30:40
Cancelled	161464	adam sherief	305	2008-10-29 02:20:03	2008-10-02 20:30:40
2 Share Ordered	138136	ADRIAN  MORRIS	252	2005-11-09 04:00:17	2005-11-05 10:24:30
2 Share Ordered	161564	Adrian Brindley	264	2008-11-05 02:20:05	2008-10-02 15:32:23
Cancelled	161352	Adrian Brindley	264	2008-10-29 02:20:05	2008-10-02 15:32:23

Open in new window

0
chaitu chaituCommented:

update table t1 set status='Cancelled' where
shareid=(select  min(shareid) from table t2
where t1.shareid=t2.shareid and t2.RFname in('Adam Arnold','Adam Collins','adam sherief','Adrian Brindley','ADRIAN  MORRIS'));

Open in new window

0
SujithData ArchitectCommented:
Try this.
update custorder A
set status = 'Cancelled'
where BrokerSent <> ( select min(BrokerSent)
                      from custorder B
		      where B.RFname = A.RFname);

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chaitu chaituCommented:
ignore previous one;
0
learningnetAuthor Commented:
hello folks,
thanks for your suggestions, will try this now

i just wanted to see using the SELECT statement if this is ok as i am updating live records i just want to take a chance ..

please can you advise me here?

select custorder A
set status = 'Cancelled'
where BrokerSent <> ( select min(BrokerSent)
                      from custorder B
                      where B.RFname = A.RFname);

thanks
0
chaitu chaituCommented:
following records aer eligilble for CANCEL
select OrderID,RFname,Shareid,BrokerSent from customer
where BrokerSent <> ( select min(BrokerSent)
                      from custorder B
                      where B.RFname = A.RFname);
0
SujithData ArchitectCommented:
>> as i am updating live records i just want to take a chance ..

If you are unsure, create a copy of your table and try the update on it first.
0
learningnetAuthor Commented:
chaituu
thanks for the SELECT statement

sujith89

ok, i have created a separate table and tried your query, i noticed that only few duplicate records are getting picked and for some reason its ignoring the rest

if you please see in the attached, only "adam sherief" records and rest still appearing it ...

i am unable to figure why its skipping and applying the change on few?

please advise



	2 Share Ordered	161500	Adam Collins	2008-10-29 02:20:02	2008-09-10 07:40:03
	2 Share Ordered	161946	Adam Collins	2008-11-12 02:20:02	2008-09-10 07:40:03
	2 Share Ordered	161698	Adam Collins	2008-11-05 02:20:03	2008-09-10 07:40:03
	2 Share Ordered	161693	adam sherief	2008-11-05 02:20:03	2008-10-02 20:30:40
	2 Share Ordered	161913	adam sherief	2008-11-12 02:20:03	2008-10-02 20:30:40
	2 Share Ordered	161779	Adrian Brindley	2008-11-12 02:20:06	2008-10-02 15:32:23
	2 Share Ordered	161564	Adrian Brindley	2008-11-05 02:20:05	2008-10-02 15:32:23

Open in new window

0
learningnetAuthor Commented:
does the column type makes any diference? i mean, BrokerSent / DateOrder fields are all having "nvarchar 50" as their data type

0
SujithData ArchitectCommented:
Isn't it the correct behaviour?
SQL> select * from custorder;
 
STATUS                  ORDERID RFNAME                                             BROKERSENT          DATEORDER
-------------------- ---------- -------------------------------------------------- ------------------- -------------------
2 Share Ordered          161500 Adam Collins                                       2008-10-29 02:20:02 2008-09-10 07:40:03
2 Share Ordered          161946 Adam Collins                                       2008-11-12 02:20:02 2008-09-10 07:40:03
2 Share Ordered          161698 Adam Collins                                       2008-11-05 02:20:03 2008-09-10 07:40:03
2 Share Ordered          161693 adam sherief                                       2008-11-05 02:20:03 2008-10-02 20:30:40
2 Share Ordered          161913 adam sherief                                       2008-11-12 02:20:03 2008-10-02 20:30:40
2 Share Ordered          161779 Adrian Brindley                                    2008-11-12 02:20:06 2008-10-02 15:32:23
2 Share Ordered          161564 Adrian Brindley                                    2008-11-05 02:20:05 2008-10-02 15:32:23
 
7 rows selected.
 
SQL> update custorder A
  2  set status = 'Cancelled'
  3  where BrokerSent <> ( select min(BrokerSent)
  4                        from custorder B
  5                   where B.RFname = A.RFname);
 
4 rows updated.
 
SQL> select * from custorder;
 
STATUS                  ORDERID RFNAME                                             BROKERSENT          DATEORDER
-------------------- ---------- -------------------------------------------------- ------------------- -------------------
2 Share Ordered          161500 Adam Collins                                       2008-10-29 02:20:02 2008-09-10 07:40:03
Cancelled                161946 Adam Collins                                       2008-11-12 02:20:02 2008-09-10 07:40:03
Cancelled                161698 Adam Collins                                       2008-11-05 02:20:03 2008-09-10 07:40:03
2 Share Ordered          161693 adam sherief                                       2008-11-05 02:20:03 2008-10-02 20:30:40
Cancelled                161913 adam sherief                                       2008-11-12 02:20:03 2008-10-02 20:30:40
Cancelled                161779 Adrian Brindley                                    2008-11-12 02:20:06 2008-10-02 15:32:23
2 Share Ordered          161564 Adrian Brindley                                    2008-11-05 02:20:05 2008-10-02 15:32:23
 
7 rows selected.

Open in new window

0
learningnetAuthor Commented:
No, this has only updated

        2 Share Ordered 161500  Adam Collins    2008-10-29 02:20:02     2008-09-10 07:40:03
        2 Share Ordered 161946  Adam Collins    2008-11-12 02:20:02     2008-09-10 07:40:03
        2 Share Ordered 161698  Adam Collins    2008-11-05 02:20:03     2008-09-10 07:40:03
        Cancelled             161913  adam sherief    2008-11-12 02:20:03     2008-10-02 20:30:40
        2 Share Ordered 161693  adam sherief    2008-11-05 02:20:03     2008-10-02 20:30:40
        2 Share Ordered 161913  adam sherief    2008-11-12 02:20:03     2008-10-02 20:30:40
        2 Share Ordered 161779  Adrian Brindley 2008-11-12 02:20:06     2008-10-02 15:32:23
        2 Share Ordered 161564  Adrian Brindley 2008-11-05 02:20:05     2008-10-02 15:32:23

and other few records from the table... i dont really understand why though !
0
SujithData ArchitectCommented:
What is the data type of BrokerSent?
I assumed that it is "DATE".
Looks like you have it as varchar2. Hopefully you dont have any spaces before and after the data.

Try the below update.

Bottom line - Use PROPER data types
update custorder A
set status = 'Cancelled'
where to_date(ltrim(rtrim(BrokerSent)),yyyy-mm-dd hh24:mi:ss) <> ( select min(to_date(ltrim(rtrim(BrokerSent)),yyyy-mm-dd hh24:mi:ss))
                      from custorder B
		      where B.RFname = A.RFname);

Open in new window

0
awking00Commented:
See attached.
update.txt
0
learningnetAuthor Commented:
hello awking00
thanks for the update
i am getting error following error

Error in list of function arguments: 'OVER' not recognized.
Missing FROM clause.
Error in ORDER BY clause.
Unable to parse query text.

when i tried this statement

select OrderID,RFname,Shareid,BrokerSent from customer
where rowid in
(select row_id from
 (select rowid row_id
  ,row_number() over (partition by shareid, dateorder order by dateorder, brokersent) rn
  from custorder)
 where rn > 1);

please advise
0
SujithData ArchitectCommented:
Have you tried this?
update custorder A
set status = 'Cancelled'
where to_date(ltrim(rtrim(BrokerSent)),yyyy-mm-dd hh24:mi:ss) <> ( select min(to_date(ltrim(rtrim(BrokerSent)),yyyy-mm-dd hh24:mi:ss))
                      from custorder B
		      where B.RFname = A.RFname);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
The solution I provided was for Oracle since I saw the question in the PL/SQL zone. I don't know if MS SQL has anything similar to the analytical queries of Oracle. Sorry :-(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.