OraFAQ Forum: SQL & PL/SQL » want to delete 42k duplicate rows from table 18 mill rows

Home » SQL & PL/SQL » SQL & PL/SQL » want to delete 42k duplicate rows from table 18 mill rows (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

want to delete 42k duplicate rows from table 18 mill rows [message #639108]

Wed, 01 July 2015 16:29

cplusplus1
Messages: 58
Registered: October 2012
Location: usa

Member

I have a table with 18 million rows and has 5 column combination primary key. column rev_center varchar(15), this one has same rev_center, one with 5 length and 15 length with all spaces.
rev_center '73000' i have same like this with spaces '73000 '

i want to delete those duplicates. which are close to 42k out of 18 million rows.

declare @GLNAME table (K_ACCT_NUMBER INT, REV_CENTER VARCHAR(15), SERVICE_DATE DATE, POSTING_DATE DATE, PDM VARCHAR(50))
insert @GLNAME
select 4543,'73000', to_date('01/22/2012'),to_date('01/24/2012'),'432' union all
select 4543,'73000 ', to_date('01/22/2012'),to_date('01/24/2012'),'432' union all ---this is duplicate can be deleted
select 3476,'55000', to_date('04/14/2012'),to_date('04/16/2012'),'634' union all
select 3476,'55000 ', to_date('04/14/2012'),to_date('04/16/2012'),'634' union all ---this is duplicate can be deleted
select 7666,'23450', to_date('05/18/2012'),to_date('05/18/2012'),'234'

select * from @GLNAME;

Thank you very much for the helpful info.

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639109 is a reply to message #639108]

Wed, 01 July 2015 16:32

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

So what's stopping you from just deleting them?

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639110 is a reply to message #639109]

Wed, 01 July 2015 16:41

cplusplus1
Messages: 58
Registered: October 2012
Location: usa

Member

Need advise please, can i use the following delete since it has 18 mill rows, don't want to make a mistake:

delete from @GLNAME where rowid in (
select row_id from (
select rowid row_id,
row_number() over (partition by K_ACCT_NUMBER,trim(REV_CENTER),SERVICE_DATE,POSTING_DATE,PDM) rn
from @GLNAME
) where rn <> 1
);

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639112 is a reply to message #639110]

Wed, 01 July 2015 16:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>don't want to make a mistake:

As long as you do NOT issue COMMIT, you can always issue ROLLBACK & no one will know the DELETE was issued.

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639114 is a reply to message #639112]

Wed, 01 July 2015 20:48

cplusplus1
Messages: 58
Registered: October 2012
Location: usa

Member

great seniors thks. should retire soon.

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639115 is a reply to message #639114]

Wed, 01 July 2015 22:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post complete results from following SQL below

SELECT * FROM V$VERSION;

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639120 is a reply to message #639115]

Thu, 02 July 2015 00:13

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Have a look at suggestions posted on our FAQ pages: How does one eliminate duplicates rows from a table?

[Updated on: Thu, 02 July 2015 00:14]

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639136 is a reply to message #639108]

Thu, 02 July 2015 02:13

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Is this an Orac;e database you're using. I don't recognise that syntax...

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639166 is a reply to message #639136]

Thu, 02 July 2015 09:14

cplusplus1
Messages: 58
Registered: October 2012
Location: usa

Member

i used the query and it worked fine, lol what a shame...seniors

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639168 is a reply to message #639136]

Thu, 02 July 2015 09:29

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

It looks like SQL Server (T-SQL) to me...

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639169 is a reply to message #639120]

Thu, 02 July 2015 09:36

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Method 4:

Create an exceptions table (UTLEXCPT.SQL) and . .

ALTER TABLE Gl_Name
  ADD CONSTRAINT No_Dup_Ows
  UNIQUE  (K_Acct_Number,Rev_Center,Service_Date,Posting_Date,Pdm)
  EXCEPTIONS INTO Exceptions;

Then just delete the rows:

DELETE FROM Gl_Name G
      WHERE EXISTS
               (SELECT '?'
                  FROM Exceptions X
                 WHERE X.Row_Id = G.ROWID)

Shocked

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639172 is a reply to message #639166]

Thu, 02 July 2015 10:02

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

cplusplus1 wrote on Thu, 02 July 2015 19:44

i used the query and it worked fine, lol what a shame...seniors

What do you mean by that last word?

P.S. : By the way, what do you mean by "seniors"? Age? Lol! Smile

Smile

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639173 is a reply to message #639172]

Thu, 02 July 2015 10:03

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

pablolee wrote on Thu, 02 July 2015 12:43

Is this an Orac;e database you're using. I don't recognise that syntax...

gazzag wrote on Thu, 02 July 2015 19:59

It looks like SQL Server (T-SQL) to me...

+1

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639174 is a reply to message #639169]

Thu, 02 July 2015 10:05

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

LKBrwn_DBA wrote on Thu, 02 July 2015 20:06

Method 4:

Create an exceptions table (UTLEXCPT.SQL)

What if OP doesn't have CREATE TABLE privilege?

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639175 is a reply to message #639172]

Thu, 02 July 2015 10:15

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

I think he's mocking a bunch of experienced Oracle experts for answering a question about SQL Server Laughing

Laughing

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639177 is a reply to message #639175]

Thu, 02 July 2015 10:24

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I need the banish wand, have had enough in the past?

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639178 is a reply to message #639166]

Thu, 02 July 2015 10:26

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

cplusplus1 wrote on Thu, 02 July 2015 09:14

i used the query and it worked fine, lol what a shame...seniors

That didn't answer the question of if you are doing this on an oracle database. It's not valid syntax in Oracle. Please prove me wrong.

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639181 is a reply to message #639177]

Thu, 02 July 2015 10:38

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Wow! I missed that thread the first time around Smile

Smile

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639291 is a reply to message #639181]

Sun, 05 July 2015 00:32

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Dear cplusplus1

Quote:

Need advise please, can i use the following delete since it has 18 mill rows, don't want to make a mistake:

This senior wants to know if you have ever heard of a thing called "testing"? Testing is how we seniors keep from making mistakes.

"senior" Kevin Meade

Report message to a moderator

Re: want to delete 42k duplicate rows from table 18 mill rows [message #639313 is a reply to message #639166]

Mon, 06 July 2015 02:32

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

cplusplus1 wrote on Thu, 02 July 2015 15:14

i used the query and it worked fine, lol what a shame...seniors

I'm not quite sure why you're taking the pissy attitude with me, I asked a perfectly reasonable question.
Just for clarity, are you saying that you ran that code in an Oracle database, and it executed correctly?

Report message to a moderator

Previous Topic:	Last four weeks data
Next Topic:	DRL for a particular condition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 21:48:25 CDT 2024