Code:
create table coltype (coltype varchar(5));
insert into coltype values ('typ1');
create table colsubtype (coltype varchar(5), colsubtype varchar(5));
insert into colsubtype values ('typ2', 'st1');
insert into colsubtype values ('typ2', 'st2');
create table table1 (col1 varchar(5), coltype varchar(5), colsubtype varchar(5));
insert into table1 values ('val1','typ1', 'st1');
insert into table1 values ('val2','typ1', 'st2');
insert into table1 values ('val3','typ1', 'st3');
insert into table1 values ('val4','typ2', 'st1');
insert into table1 values ('val5','typ2', 'st2');
insert into table1 values ('val6','typ2', 'st3');
insert into table1 values ('val7','typ3', 'st1');
insert into table1 values ('val8','typ3', 'st2');
insert into table1 values ('val9','typ3', 'st3');
commit;
Basically, I want to delete all records where the coltype
and colsubtype
is not mentioned in the coltype
and colsubtype
tables.
How do I do that. The below is path I was thinking of taking but it does not work - and - it does not seem like a good design.
delete from table1
where coltype != (select coltype from coltype)
OR not (coltype = cst.coltype and colsubtype = cst.colsubtype
from (select coltype, colsubtype from colsubtype) cst)
Give this a try
delete from table1
where not exists
(
select *
from coltype
where table1.coltype = coltype.coltype
)
and not exists
(
select *
from colsubtype
where table1.coltype = colsubtype.coltype
and table1.colsubtype = colsubtype.colsubtype
)