OraFAQ Forum: SQL & PL/SQL » set target table flag as D if rows deleted at source

Home » SQL & PL/SQL » SQL & PL/SQL » set target table flag as D if rows deleted at source (oracle 11.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

set target table flag as D if rows deleted at source [message #643493]

Thu, 08 October 2015 12:02

deb171100
Messages: 7
Registered: October 2015

Junior Member

Hii Expert,
Can any one tell me how to update destination table's additional flag_ind as 'D' when source record get deleted ( Example id 2 is missing in source table.
Can we do it using merge?? What is the best approach in this case?

TABLE_1:

ID SAL
1 500
3 700

TABLE_2:

ID SAL FLAG_IND
1 500
2 600
3 700

RESULT:

TABLE_2:

ID SAL FLAG_IND
1 500
2 600 D
3 700

Report message to a moderator

Re: set target table flag as D if rows deleted at source [message #643494 is a reply to message #643493]

Thu, 08 October 2015 13:55

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

update table2 t2 set flag_ind='D'
where not exists (select null 
                  from table1 t1 
                  where t1.id1 = t2.id 
                    and maybe sal if sal is part of the key for your requirements)
/

Report message to a moderator

Re: set target table flag as D if rows deleted at source [message #643499 is a reply to message #643493]

Fri, 09 October 2015 02:28

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

IF you want to update target table as in when records are deleted on source table then triggers are good idea.

If you want to do it only once (I assume records are already deleted in source table and you just want to update target table just once) then of course single update query suggested by Michel is enough.

Report message to a moderator

Previous Topic:	required date month year query
Next Topic:	Rownum per day

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 04:36:40 CDT 2024