sql - Deleting rows from parent and child tables -

-

assume 2 tables in oracle 10g

tablea (parent) --> tableb (child)

every row in tablea has several child rows related in tableb.

i want delete specific rows in tablea means have delete related rows in tableb first.

this deletes child entries

delete tableb last_update_dtm = sysdate-30;

to delete parent rows rows deleted in child table this

delete tablea not exists (select 1 tableb tablea.key=tableb.key);

the above will delete rows in child table (last_update_dtm = sysdate-30) false. tablea not have last_update_dtm column there no way of knowing rows delete without entries in child table.

i save keys in child table prior deleting seems expensive approach. correct way of deleting rows in both tables?

edit

to explain better trying achieve, following query have done trying if there no constraint between 2 table. 

delete tablea exists ( select 1 tableb tablea.key=tableb.key , tableb.last_update_dtm=sysdate-30)  delete tableb last_update_dtm=systdate-30

two possible approaches.

  1. if have foreign key, declare on-delete-cascade , delete parent rows older 30 days. child rows deleted automatically.

  2. based on description, looks know parent rows want delete , need delete corresponding child rows. have tried sqls this?

      delete child_table       parent_id in (            select parent_id parent_table                 updd_tms != (sysdate-30)

    -- delete parent table records

    delete parent_table updd_tms != (sysdate-30);

---- based on requirement , looks might have use pl/sql. i'll see if can post pure sql soution (in case definetely way go).

declare     v_sqlcode number;     pragma exception_init(foreign_key_violated, -02291); begin     v_rec in (select parent_id, child id child_table                          updd_tms != (sysdate-30) ) loop      -- delete children     delete child_table child_id = v_rec.child_id;      -- delete parent. if foreign key violation,      -- stop step , continue loop     begin        delete parent_table           parent_id = v_rec.parent_id;     exception        when foreign_key_violated          null;     end;  end loop; end; /

Comments

Post a Comment

Popular posts from this blog

c# - How to set Z index when using WPF DrawingContext? -

-
how set z-index drawing object when using drawingcontext.drawxxx() methods? the object drawn last have higher z index. can't change index of drawn objects. way draw in order. if using wpf (as placed tag), can use, example, canvas control. create shapes need like polyline obj = new polyline(); //... // ... set properties of obj and add them canvas uielementcollection: yourcanvasname.children.add(obj); //or yourcanvasname.children.insert(i, obj); first items of collection have higher z index. advantages in way: no need redraw on window changes, can anytime move objects , change order.
Read more

razor - Is this a bug in WebMatrix PageData? -

-
i think may have found bug in webmatrix's pagedata, not sure. concerns how pass data partial page calling page. in webmatrix documentation (tutorials, e.g. " 3 - creating consistent look ", , example code), pagedata recommended mechanism pass data between pages (e.g. content page layout page, or partial page). however have found not work other way, pass data partial page calling page. modifying or adding entries in pagedata in partial page, not seem calling page. cutting right down simplest possible example, in test page may have this: @{ pagedata["test"] = "initial entry"; } <p>before calling partial page, test value @pagedata["test"]</p> @renderpage("_testpartial.cshtml") <p>after returning calling page, test value @pagedata["test"]</p> and in _testpartial.cshtml page might have this: @{ pagedata["test"] = "modified entry"; } <p>in partial page...
Read more

android - layout with fragment and framelayout replaced by another fragment and framelayout -

-
edit: so after comments below, revisted , realized hanging me up. imagine client list , client details activity started : public class clientsmainactivity extends fragmentactivity { @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); //studiotabopenhelper db; setcontentview(r.layout.main_client_activity); } } so works great, starts main_client_activity (defined in layout below, , call activity when button on main screen clicked): intent intent = new intent(getactivity(), clientsmainactivity.class); startactivity(intent); easy issue is, clientsmainactivity not call oncreateview or anything, sets layout layout defines fragment, , listfragment. fine cause not trying pass clientsmainactivity , if have hypothetical activity like: sessionmainsactivity called when click on session edit of client, not calling sessionsmainactivity same way (starts activity sets alayout), want layout set defines...
Read more