Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

remove duplicate in db2 throu sql and reatin only one column
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: remove duplicate in db2 throu sql and reatin only one column
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: 1278
Location: Belgium

PostPosted: Fri Mar 12, 2010 2:27 pm    Post subject:
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Fri Mar 12, 2010 2:48 pm    Post subject: Reply to: remove duplicate in db2 throu sql and reatin only
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Fri Mar 12, 2010 6:49 pm    Post subject:
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    Post subject: Reply to: remove duplicate in db2 throu sql and reatin only
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    Post subject:
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 12, 2010 9:03 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri Mar 12, 2010 10:01 pm    Post subject:
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: 1278
Location: Belgium

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

http://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    Post subject: Reply to: remove duplicate in db2 throu sql and reatin only
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: 1278
Location: Belgium

PostPosted: Mon Mar 15, 2010 2:27 pm    Post subject:
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    Post subject: Reply to: remove duplicate in db2 throu sql and reatin only
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: 1278
Location: Belgium

PostPosted: Mon Mar 15, 2010 5:17 pm    Post subject:
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: 1013
Location: India

PostPosted: Tue Mar 16, 2010 7:16 am    Post subject:
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    Post subject:
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    Post subject:
Reply with quote

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

Site Director


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

PostPosted: Wed Mar 17, 2010 7:46 pm    Post subject:
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us