IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

remove duplicate in db2 throu sql and reatin only one column


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Thu Mar 11, 2010 9:31 pm
Reply with quote

Hi,

Please tell me any wy in db2 sql to remove duplicate and regain only orginal row

For e.g. Table
empid empname salary
1111 satish kumar 20,000
2222 sanjay kumar 10,000
3333 archana kh 11,000
4444 ashutosh 12,000
5555 mughdha 15000
1111 satish kumar 20,000
1111 satish kumar 20,000
1111 satish kumar 20,000
2222 sanjay kumar 10,000
3333 archana kh 11,000
3333 archana kh 11,000
4444 ashutosh 12,000

now my result look like this
empid empname salary
1111 satish kumar 20,000
2222 sanjay kumar 10,000
3333 archana kh 11,000
4444 ashutosh 12,000
5555 mughdha 15000

I had tried below query but it will deleted entire row

1. DELETE FROM EMPLOYEE A
WHERE 1 < (SELECT COUNT (NAME) FROM
EMPLOYEE B
WHERE A.NAME = B.NAME);
2. Delete from employee
Where Name in (select Name from
Employee group by name
Having count (*) > 1)

3. Delete from Employee E1
Where empid <> (select Min (empid) from
Employee E2
Where E2. Empid = E1.empid)

please help me regarding this
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Mar 12, 2010 2:27 pm
Reply with quote

if there is no other column like timestamp_last_update on which you can decide which row to keep,
then the only way I can think of is :

create table new_table like ...
insert in new_table (select col1,col2,col3 from old-table group by col1,col2,col3)
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Fri Mar 12, 2010 2:42 pm
Reply with quote

Hi ,

there is other way to store table and after that insert again but i want to do in single sql statement just like this :
1.. DELETE FROM EMPLOYEE A
WHERE 1 < (SELECT COUNT (NAME) FROM
EMPLOYEE B
WHERE A.NAME = B.NAME);
2. Delete from employee
Where Name in (select Name from
Employee group by name
Having count (*) > 1)
My requiremnt is i have table
empid empname salary
1111 satish kumar 20,000
2222 sanjay kumar 10,000
3333 archana kh 11,000
4444 ashutosh 12,000
5555 mughdha 15000
1111 satish kumar 20,000
1111 satish kumar 20,000
1111 satish kumar 20,000
2222 sanjay kumar 10,000
3333 archana kh 11,000
3333 archana kh 11,000
4444 ashutosh 12,000

now i am using query

1.DELETE FROM EMPLOYEE A
WHERE 1 < (SELECT COUNT (empid ) FROM
EMPLOYEE B
WHERE A.NAME = B.NAME);
2. Delete from employee
Where empid in (select empid from
Employee group by empid
Having count (*) > 1)

It will delete entire duplicate table i want my result like this using single sql not once store in some where then remove nad then insert

empid empname salary
1111 satish kumar 20,000
2222 sanjay kumar 10,000
3333 archana kh 11,000
4444 ashutosh 12,000
5555 mughdha 15000
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Mar 12, 2010 2:48 pm
Reply with quote

the issue has been debated almost to death
searching for DUPICATE ROWS and choosing the DB2 forum will give You lots of topics where the issue is discussed
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Fri Mar 12, 2010 5:54 pm
Reply with quote

Hi

issue is discussed regarding removal of duplicates not contain orginal low.
My question is if i retain original row and delet duplicate one throu sql then there is any way to do that because belwo query delete entire record dupliacte as well as orginal one .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Mar 12, 2010 6:49 pm
Reply with quote

what version of DB2 ?
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Fri Mar 12, 2010 8:30 pm
Reply with quote

Hi,

iversion is 9
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Fri Mar 12, 2010 8:31 pm
Reply with quote

V9R1
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Mar 12, 2010 9:03 pm
Reply with quote

since you don't specify the primary key of the table (or the table doesn't have one), you might use RID()

Code:
DELETE FROM EMPLOYEE A
WHERE rid(employee) > (SELECT min(rid(employee)) FROM
EMPLOYEE B where A.NAME = B.NAME and a.empid = b.empid)
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Fri Mar 12, 2010 9:35 pm
Reply with quote

RID not works here i face -440 error when i execute this query
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Mar 12, 2010 10:01 pm
Reply with quote

Hello,

It may help if you post (using copy/paste) the exact sql you tried.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Mar 12, 2010 10:09 pm
Reply with quote

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/DSNSQK10/3.2.110?DT=20070125023435

Are you in NFM (New Function Mode)?
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Mon Mar 15, 2010 12:13 pm
Reply with quote

SELECT RID(employee) FROM EMPLOYEE; 00010017
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME RID HAVING COMPATIBLE
ARGUMENTS WAS FOUND IN THE CURRENT PATH
DSNT418I SQLSTATE = 42884 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORFN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 15, 2010 2:27 pm
Reply with quote

we're only in ENFM so I can't test it myself.

are you in NFM ?

Somewhere in JESMSGLG of xxxxMSTR with xxxx being your DB2 Subsystem ID (like DB2P for production and DB2T for test)

you should find the message :

DSNG007I - DB2 CATALOG LEVEL (910) CODE LEVEL (910) MODE (N)

If mode is N, then you should check with your DBA and/or let him verify with IBM.
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Mon Mar 15, 2010 3:16 pm
Reply with quote

Hi,

I try to build sql which delete duplicate and retain original value.

So i want a way to do that ,but rowid not support in current system .

right now i create senior in test not in production.

so there is other way to that.

because thro jcl i want to do that so that's why i chose spiff to execute first.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 15, 2010 5:17 pm
Reply with quote

NO thre isnt other way to that witouht unic indxs
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Mar 16, 2010 7:16 am
Reply with quote

Have you tried using Data-Change-Table clause like the following

Code:
INSERT INTO BOBT1.DUPCOUNT
SELECT DISTINCT ID, "NAME", DESCRIPTION FROM
(SELECT * FROM OLD TABLE (
DELETE FROM BOBT1.DUPCOUNT
WHERE ID IN (SELECT ID FROM BOBT1.DUPCOUNT GROUP BY ID
HAVING COUNT(*) >
1) ))AS TABLE1


I wrote the above query to test it on my test table. It did'nt work for me since i am not in v9. Can you just copy the essence & try and let me know if it works.

Sushanth
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Wed Mar 17, 2010 12:37 pm
Reply with quote

Hi Please right this query on base of this table right now i am able to write but some missing syntax ,could you please right on below table
empid empname salary
1111 satish kumar 20,000
2222 sanjay kumar 10,000
3333 archana kh 11,000
4444 ashutosh 12,000
5555 mughdha 15000
1111 satish kumar 20,000
1111 satish kumar 20,000
1111 satish kumar 20,000
2222 sanjay kumar 10,000
3333 archana kh 11,000
3333 archana kh 11,000
4444 ashutosh 12,000
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Wed Mar 17, 2010 1:24 pm
Reply with quote

sorry DB2 version is 8.1.5
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Mar 17, 2010 7:46 pm
Reply with quote

Hello,

Quote:
right now i am able to write but some missing syntax
If you post what you have, someone may be able to help.
Back to top
View user's profile Send private message
manikawnth

New User


Joined: 07 Feb 2007
Posts: 61
Location: Mumbai

PostPosted: Thu Mar 18, 2010 11:41 am
Reply with quote

I donno if u r looking for a solution or trying to dig up DB2.
Y dont u use a serial cursor with ORDER BY clause on all the columns with a FOR UPDATE OF clause.

Fetch first row and store the entire row in W-S.
Fetch NEXT compare it with W-S.
If equal DELETE CURRENT OF cursor.
If different move it to W-S.

Run a one time BMP like this and declare an index on all the columns to be unique to avoid duplications in future.

Thanks,
Manikanth
P.S: Do I make sense or Is my solution useless?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
No new posts Remove leading zeroes SYNCSORT 4
Search our Forums:

Back to Top