teepu teepu - 3 months ago 10
SQL Question

How to compare ROWID

I have a table

t1
and table
t2
.Were as in table
t1
primary constraints will be in disabled status. Primary constraint is on column
C1,C2
And table
t2
do not have any constraint.

When I try to enable Primary constraint
PK_test
in table
t1
if there is any exception(records violates constraint) will be saved in table
t2
.Table
t1
will have columns
c1,c2
as shown below.

C1 C2 ROWID
1 2 ABCD
1 2 ABCE
1 2 ABCF
7 8 ABCI


And table
t2
will have column
ROWID
with data

ROWID
ABCD
ABCE
ABCF


since only these rows violating
PK


So I wanted to keep only the
min(ROWID)
in table
t2
for the columns
C1,C2
.
So I have written a query

delete *
from t2
where ROWID not in(select min(ROWID)
from t1
where ROWID in (select ROWID
from t2)
group by C1,C2);


But the above query is writing NULL also
select min(rowed)..
query returning
1 ROWS(ABCD)
correctly.

Please help to find what exactly is wrong here.

Answer

Here I'm trying to delete t2.ROWID that are greater than min(ROWID) of any t1 that has the same C1 and C2

delete t2 
where t2."ROWID" > (select min(a."ROWID") 
                      from t1 join t1 a on t1.c1 = a.c1 and t1.c2 = a.c2
                     where t1."ROWID" = t2."ROWID");