View previous topic :: View next topic
|
Author |
Message |
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
what version of DB2 ? |
|
Back to top |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
Hi,
iversion is 9 |
|
Back to top |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
V9R1 |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
RID not works here i face -440 error when i execute this query |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
It may help if you post (using copy/paste) the exact sql you tried. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
Back to top |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
NO thre isnt other way to that witouht unic indxs |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
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 |
|
|
santosh100678
New User
Joined: 21 Sep 2009 Posts: 55 Location: Kolkatta
|
|
|
|
sorry DB2 version is 8.1.5 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
manikawnth
New User
Joined: 07 Feb 2007 Posts: 61 Location: Mumbai
|
|
|
|
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 |
|
|
|