|
View previous topic :: View next topic
|
| Author |
Message |
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 194
Location: chennai
|
|
|
|
Hi all
Can anyone post a replay in this regard which will be very helpfull to me.
The requirement is
I have to delete duplicate rows form the table keeping one of the duplicate row.
Duplicate in the sense, duplicate by the value of a coulmn. Here EMPNO.
The Issue is
It is working fine in DB2 V7.1, but not in DB2 V8.1.
The query as well as the result is given below. Any suggestion is valuable.
In DB2 V7.1
| Code: |
SELECT * FROM REF;
---------+---------+---------+---------+---------+---------+---------+
EMPNO EMPNAME
---------+---------+---------+---------+---------+---------+---------+
1234 RAM
1234 RAMRAJ
1234 RAMAN
1234 RAMKUMAR
1234 RAMANI
1233 ABY
1233 ABY K
1233 ABY MATHEW
1236 ABY KURUV
DSNE610I NUMBER OF ROWS DISPLAYED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+
DELETE FROM REF A WHERE EMPNO IN
(SELECT EMPNO FROM REF
WHERE EMPNO = A.EMPNO
GROUP BY EMPNO
HAVING COUNT(*) > 1);
---------+---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 6
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM REF;
---------+---------+---------+---------+---------+---------+---------+
EMPNO EMPNAME
---------+---------+---------+---------+---------+---------+---------+
1234 RAMANI
1233 ABY MATHEW
1236 ABY KURUV
DSNE610I NUMBER OF ROWS DISPLAYED IS 3
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+
|
Here one row each of EMPNO 1234 and 1233 is keeping in the table and the
remaining rows of value 1234 and 1233 get deleted.
Row belongs to EMPNO 1236 is keeping in the table since there is only
one row having this value.
DB2 V8.1
| Code: |
SELECT * FROM REF;
---------+---------+---------+---------+---------+---------+-------
EMPNO EMPNAME
---------+---------+---------+---------+---------+---------+-------
1234 RAM
1234 RAMRAJ
1234 RAMAN
1234 RAMKUMAR
1236 ABY KURUV
1233 ABY THAMPY
1233 ABY K
1233 ABY
1234 RAMANI
DSNE610I NUMBER OF ROWS DISPLAYED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-------
DELETE FROM REF A WHERE EMPNO IN
(SELECT EMPNO FROM REF
WHERE EMPNO = A.EMPNO
GROUP BY EMPNO
HAVING COUNT(*) > 1) ;
---------+---------+---------+---------+---------+---------+-------
DSNE615I NUMBER OF ROWS AFFECTED IS 8
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-------
SELECT * FROM REF;
---------+---------+---------+---------+---------+---------+-------
EMPNO EMPNAME
---------+---------+---------+---------+---------+---------+-------
1236 ABY KURUV
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-------
|
Here if the row is duplicated by the value of EMPNO, all the rows get
deleted.
The table declaration is same in both the versions. Tablespace is also same in nature(Both Segemented).
Any suggestion is valuable.
Please help
Regards
Raghu |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10900
Location: italy
|
|
|
|
it has been a while since I fiddled with DB2, but I dare to say that You have judged the wrong way around
also IMHO even if the query had worked the way You wanted You would be anyway taking a random chance on which employee name to keep
also I feel , but I might be wrong, that the constarint empno = a.empno is not needed
why my comments ...
the <inner> query gives as result set all the occurrences of duplicated empno' s
( no need for the where clause )
the delete, as I see it, deletes all the rows for which the empno is in the previously obtained result set
so after all V8 behavior is the right one, keep testing  |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 194
Location: chennai
|
|
|
|
Hi enrico-sorichetti
Thanks for the response.
| Quote: |
| also I feel , but I might be wrong, that the constarint empno = a.empno is not needed |
Yes you are right. Without the where clause also the SQL will work, but gives the same result. i.e. deleting all duplicates rows based on the the value of EMPNO.
But my belief is that for a correlated subquery, the outer query will execute first and if it finds a row in the specified table, then only the inner query will execute. Am I right?
If I am right, the query will read a row from the table then execute the subquery. If the condition specified in the subquery is fullfilled, then it will delete the row whcih is already read. Am I right?
I also feel that DB2 8 is better than DB2 7. But in this case my request is not satisfied.
Thanks
Raghu |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Raghu,
| Quote: |
| the outer query will execute first and if it finds a row in the specified table, then only the inner query will execute. |
I think inner query gets executed first, then the outer query while executing it gets the data compared with the inner query result based on the conditions given.
I hope, iam right,
Sushanth |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Nope ... for each outer row, the empno for that row is passed to the subquery and the empno satisfying the count(*) condition is passed back to the outer query for comparison ... in the above query this correlation itself is redundant .... |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 194
Location: chennai
|
|
|
|
Hi sushanth bobby
| Quote: |
I think inner query gets executed first, then the outer query while executing it gets the data compared with the inner query result based on the conditions given.
|
Your statement is valid for Sub-query and not for correlated sub-query.
My query is a correlated sub query.
Actually the real issue is not related to the flow of execution of the query.
The issue is related to the result of the query -same query is giving different result in different versions of DB2.
Thanks anyway for responding sushanth bobby
Hi ashimer
| Quote: |
| ... in the above query this correlation itself is redundant .... |
Thanks for your response.
If I remove WHERE EMPNO = A.EMPNO form the query, then both the versions will give the same result. If I put it on the query, I got different result.
Any suggestion is thankful.
Regards
Raghu |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10900
Location: italy
|
|
|
|
I did a bit of searching around and the general consensus is that V8 behavior is the right one ...
| Code: |
DELETE FROM tablename WHERE colname IN
(SELECT colname FROM tablename
GROUP BY colname
HAVING COUNT(*) > 1); |
should delete all the duplicate rows occurrences
after having investigated any other subtle differences in the two environments with Your support group
it might be worth opening an issue with IBM support |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Raghu,
I got the results expected by you, but i don't know how this works for you. Your example had a pattern like retaining the last duplicate. This is how it goes.
THE QUERY
| Code: |
DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPTAB
(EMPNO INTEGER NOT NULL WITH DEFAULT
,NAME CHAR(25) NOT NULL) on commit preserve rows;
insert into session.emptab values(1234,'RAM');
insert into session.emptab values(1234,'RAMRAJ ');
insert into session.emptab values(1234,'RAMAN ');
insert into session.emptab values(1234,'RAMKUMAR ');
insert into session.emptab values(1234,'RAMANI ');
insert into session.emptab values(1233,'ABY ');
insert into session.emptab values(1233,'ABY K ');
insert into session.emptab values(1233,'ABY MATHEW');
insert into session.emptab values(1236,'ABY KURUV ');
select * from session.emptab;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPTB
(EMPNO INTEGER NOT NULL WITH DEFAULT
,NAME CHAR(25) NOT NULL
,PK_ROW_NUM DECIMAL(9 , 0)
GENERATED ALWAYS AS IDENTITY
(START WITH 1 ,
INCREMENT BY 1 ,
CACHE 20 ,
NO CYCLE ,
MINVALUE -999999999 ,
MAXVALUE 999999999 ,
NO ORDER )
)on commit preserve rows
;
INSERT INTO SESSION.TEMPTB (empno,name)
select empno,name from session.emptab;
SELECT * FROM SESSION.TEMPTB;
delete from session.temptb where pk_row_num not in(
select Max(pk_row_num) from session.temptb
group by empno);
select * from session.temptb; |
SPUFI RESULTS
| Code: |
---------+---------+---------+---------+---------+---------+----
DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPTAB
(EMPNO INTEGER NOT NULL WITH DEFAULT
,NAME CHAR(25) NOT NULL) on commit preserve rows;
---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1234,'RAM');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1234,'RAMRAJ ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1234,'RAMAN ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1234,'RAMKUMAR ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1234,'RAMANI ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1233,'ABY ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1233,'ABY K ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1233,'ABY MATHEW');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
insert into session.emptab values(1236,'ABY KURUV ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
select * from session.emptab;
---------+---------+---------+---------+---------+---------+----
EMPNO NAME
---------+---------+---------+---------+---------+---------+----
1234 RAM
1234 RAMRAJ
1234 RAMAN
1234 RAMKUMAR
1234 RAMANI
1233 ABY
1233 ABY K
1233 ABY MATHEW
1236 ABY KURUV
DSNE610I NUMBER OF ROWS DISPLAYED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPTB
(EMPNO INTEGER NOT NULL WITH DEFAULT
,NAME CHAR(25) NOT NULL
,PK_ROW_NUM DECIMAL(9 , 0)
GENERATED ALWAYS AS IDENTITY
(START WITH 1 ,
INCREMENT BY 1 ,
CACHE 20 ,
NO CYCLE ,
MINVALUE -999999999 ,
MAXVALUE 999999999 ,
NO ORDER )
)on commit preserve rows
;
---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
INSERT INTO SESSION.TEMPTB (empno,name)
select empno,name from session.emptab;
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
SELECT * FROM SESSION.TEMPTB;
---------+---------+---------+---------+---------+---------+----
EMPNO NAME PK_ROW_NUM
---------+---------+---------+---------+---------+---------+----
1234 RAM 1.
1234 RAMRAJ 2.
1234 RAMAN 3.
1234 RAMKUMAR 4.
1234 RAMANI 5.
1233 ABY 6.
1233 ABY K 7.
1233 ABY MATHEW 8.
1236 ABY KURUV 9.
DSNE610I NUMBER OF ROWS DISPLAYED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
delete from session.temptb where pk_row_num not in(
select Max(pk_row_num) from session.temptb
group by empno);
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 6
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
select * from session.temptb;
---------+---------+---------+---------+---------+---------+----
EMPNO NAME PK_ROW_NUM
---------+---------+---------+---------+---------+---------+----
1234 RAMANI 5.
1233 ABY MATHEW 8.
1236 ABY KURUV 9.
DSNE610I NUMBER OF ROWS DISPLAYED IS 3
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
---------+---------+---------+---------+---------+---------+----
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 16
DSNE621I NUMBER OF INPUT RECORDS READ IS 38
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 136 |
Sushanth |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 194
Location: chennai
|
|
|
|
Hi enrico-sorichetti
Thanks for your comments. I hope you understand the issue fully. I will check with the DB2 support group.
Regards
Raghu
Hi sushanth bobby
Thanks for your effort. You had done a lot of work for that. I appreciate your work. Keep it up. But what I want is "why the same query is giving diffrent result in different version?". I didn't find any reson. I will be keeping research on it.
Regards
Raghu |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10900
Location: italy
|
|
|
|
| Quote: |
| I hope you understand the issue fully. |
what makes You think I did not understand the issue  
the difference could even be a maintenance issue on db2 v7
why haven' t yet involved Your support ?
forum should not be surrogates for proper in house support |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Enrico,
| Quote: |
| I hope you understand the issue fully. |
Once again language has caused misunderstanding.
I believe Raghu's comment meant that you clearly understood what was going on rather than you did not understand. |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 194
Location: chennai
|
|
|
|
Hi dick and enrico-sorichetti
I simply trying to say that you have understood the issue raise by me and I got relevant replay from both of you. I am very much thankful to you both.
Regards
Raghu |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
