Removing duplicate records by using Oracle’s ROWID

from test

group by a,b

having count(*) > 1;

ผลลัพธ์ที่ได้ :

A          B   COUNT(*)

———- ———- ———-

1          2        259

2          2          5

group by a,b;

A          B   COUNT(*)

———- ———- ———-

1          2        259

2          2          5

3          0          1

— 4 แถวที่ซึ่ง A = 2 และ B = 2

— ลองมา select แถวที่เราจะคงไว้ดูก่อน

select min(rowid),a,b from test

group by a,b;

MIN(ROWID)                             A          B

——————————- ———- ———-

AAAAyvAAGAAAABYAAA          1          2

AAAAyvAAGAAAABYAED          2          2

AAAAyvAAGAAAABYAEI           3          0

— เริ่มกันเลย

delete from test where rowid not in (

select min(rowid) from test group by a,b);

262 rows deleted.

select rowid,a,b from test;

ROWID                                      A          B

——————————— ———- ———-

AAAAyvAAGAAAABYAAA          1          2

AAAAyvAAGAAAABYAED          2          2

AAAAyvAAGAAAABYAEI           3          0