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).
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.
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.
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Quote:
I hope, iam right
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 ....
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.
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
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.
