DELETE with OuterJoin
Hi to all, as result of an app-error there are rows in the master-table are deleted but not the associated rows in the detail-table. In a view using an outer join I can see the wrong rows - but I can not delete (view). So I search for a construct of a delete-statment that enclose instead of the table-name a"select"-statment like it is used in the "create view"-statment. I can not find any documentation who to write this - with "try and error" I have not become a result ... Igo
"Igo Besser" <i.besser@aon.at> schrieb: >So I search for a construct of a delete-statment that enclose instead of the >table-name a"select"-statment like it is used in the "create >view"-statment. What about: delete from <detail-table> where <foreign-key> not in (select <primary key> from <master-table>) This doesn't work for composite keys, if you have such then you probably have to build a statement using "exists". I don't have much experience with this, so you'll have to look that up in the manual. HTH, Richard
Here is are examples of several ways.
Of course let me suggest that you add a foreign key constraint with
delete cascade (RTFM for syntax.)
create table parent_table(
id int,
id2 int,
pname varchar(64)
) ;
create table child_table(
cid int,
pid int,
pid2 int,
dname varchar(64)
) ;
insert into parent_table values (1, 1, "first first family");
insert into parent_table values (1, 2, "second first family");
insert into parent_table values (2, 1, "first second family");
insert into parent_table values (2, 2, "second second family");
insert into child_table (cid, pid, pid2, dname) values (1, 1, 1, "dadof first first family") ;
insert into child_table (cid, pid, pid2, dname) values (2, 1, 1, "momof first first family") ;
insert into child_table (cid, pid, pid2, dname) values (3, 1, 1, "firstchild of first first family") ;
insert into child_table (cid, pid, pid2, dname) values (4, 1, 1,"second child of first first family") ;
insert into child_table (cid, pid, pid2, dname) values (1, 1, 2, "dadof second first family") ;
insert into child_table (cid, pid, pid2, dname) values (2, 1, 2, "momof second first family") ;
insert into child_table (cid, pid, pid2, dname) values (3, 1, 2, "firstchild of second first family") ;
insert into child_table (cid, pid, pid2, dname) values (4, 1, 2,"second child of second first family") ;
insert into child_table (cid, pid, pid2, dname) values (1, 2, 1, "dadof first second family") ;
insert into child_table (cid, pid, pid2, dname) values (2, 2, 1, "momof first second family") ;
insert into child_table (cid, pid, pid2, dname) values (3, 2, 1, "firstchild of first second family") ;
insert into child_table (cid, pid, pid2, dname) values (4, 2, 1,"second child of first second family") ;
insert into child_table (cid, pid, pid2, dname) values (1, 2, 2, "dadof second second family") ;
insert into child_table (cid, pid, pid2, dname) values (2, 2, 2, "momof second second family") ;
insert into child_table (cid, pid, pid2, dname) values (3, 2, 2, "firstchild of second second family") ;
insert into child_table (cid, pid, pid2, dname) values (4, 2, 2,"second child of second second family") ;
insert into child_table (cid, pid, pid2, dname) values (1, 3, 1, "dadof first third family") ;
insert into child_table (cid, pid, pid2, dname) values (2, 3, 1, "momof first third family") ;
insert into child_table (cid, pid, pid2, dname) values (3, 3, 1, "firstchild of first third family") ;
insert into child_table (cid, pid, pid2, dname) values (4, 3, 1,"second child of first third family") ;
insert into child_table (cid, pid, pid2, dname) values (1, 3, 2, "dadof second third family") ;
insert into child_table (cid, pid, pid2, dname) values (2, 3, 2, "momof second third family") ;
insert into child_table (cid, pid, pid2, dname) values (3, 3, 2, "firstchild of second third family") ;
insert into child_table (cid, pid, pid2, dname) values (4, 3, 2,"second child of second third family") ;
-- not exists way
begin work;
delete from child_table where not exists(select * from parent_table pt
where pt.id = child_table.pid and pt.id2 = child_table.pid2 );rollback work;
-- Sometimes faster using temp table.
begin work;
select pt.id, pid, pid2 from outer parent_table pt, child_table ct
where pt.id = ct.pid and pt.id2 = ct.pid2 into temp t_to_delete with nolog;
delete from t_to_delete where id is null ;rollback work;
begin work;
-- Be careful here you might want to see what might happen if the
delimeter '!' is in the ids.
delete from child_table where pid || '!' || pid2 not in (select pt.id|| '!' || pt.id2 from parent_table pt );
rollback work;
drop table parent_table;
drop table child_table;
Igo Besser wrote:
> Hi to all,
>
> as result of an app-error there are rows in the master-table are deleted but
> not the associated rows in the detail-table. In a view using an outer join I
> can see the wrong rows - but I can not delete (view).
> So I search for a construct of a delete-statment that enclose instead of the
> table-name a"select"-statment like it is used in the "create
> view"-statment.
>
> I can not find any documentation who to write this - with "try and error" I
> have not become a result ...
>
> Igo
bozon wrote:
> begin work;
> select pt.id, pid, pid2 from outer parent_table pt, child_table ct
> where pt.id = ct.pid and pt.id2 = ct.pid2 into temp t_to_delete with no> log;
> delete from t_to_delete where id is null ;> rollback work;
What I meant to say was:
begin work;
select pt.id, pid, pid2 from outer parent_table pt, child_table ct
where pt.id = ct.pid and pt.id2 = ct.pid2 into temp t_to_delete with nolog;
delete from child_table where exists(select * from t_to_delete t wheret.id is null and t.pid = child_table.pid and t.pid2 =
child_table.pid2);
rollback work;