|
View previous topic :: View next topic
|
| Author |
Message |
revathi
New User
Joined: 07 Mar 2005
Posts: 3
|
|
|
|
1.how to delete duplicate records in a table?
2.how can we find no of rows updated in the table quickly after updation operation? |
|
| Back to top |
|
 |
ovreddy
Active User
.jpg)
Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.
|
|
|
|
Hi Revathi,
You can delete duplicate records in 2 steps...
1. Select count (1), id, name, salary
from employee (nolock)
group by id, name,salary into :hv-id,:hv-name,:hv-salary having count(1)>1;
2. Delete from employee where id=:hv-id and name=:hv-name
and salary=:hv-salary;
Number of rows updated:
When an UPDATE statement completes execution, the value of SQLERRD(3) in the SQLCA is the number of rows updated.
Bye,
Reddy. |
|
| Back to top |
|
|
somasundaran_k
Active User
.jpg)
Joined: 03 Jun 2003
Posts: 134
|
|
|
|
Reddy,
I think we can delete the duplicate records in one parse.
| Code: |
DELETE
FROM TABLE A
WHERE (A.COL1,A.COL2,A.COL3)
IN (SELECT B.COL1,B.COL2,B.COL3
FROM TABLE B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 = B.COL3
GROUP BY B.COL1, B.COL2, B.COL3
HAVING COUNT(*) > 1);
|
hth
-Som |
|
| Back to top |
|
|
|
|
