Table Updation using Query



IBM's flagship relational database management system

Table Updation using Query

Postby gopi palanisamy » Mon Jun 21, 2010 4:01 pm

Hi ,
I have the following requirement in my team .Request you to help me on how this is possible using a join Query
Requirment:

I have two Table - say Table 1 and Table 2.
-------------------------------------------
Query for Table 1:
select distinct(ID) from Table 1
where (condn)
----------------------------------------------
Query for Table :
select distinct(ID) from Table 2
where (condn)
---------------------------------------------
My criteria is to delete and insert into Table 2 for the following scenario:
Table 1 Table 2 Action 1
----------------------------------------------
present present no action
not presen present delete from Table 2
present not present insert into Table 2
------------------------------------------------
Please advise me on how to proceed with the Changes using Joins.Is it possible to do so.If so can you please help as it an urgent requirement popped up in my team.Could you also provide the code procedure on how to proceed.Thank you.
gopi palanisamy
 
Posts: 3
Joined: Mon Jun 21, 2010 3:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Table Updation using Query

Postby dick scherrer » Mon Jun 21, 2010 11:17 pm

Hello and welcome to the forum,

If so can you please help as it an urgent requirement popped up in my team.
You need to understand that we don't do urgent. . .

Why do you believe "distinct" should be considered?

Why do you believe this must be done with a single query?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Table Updation using Query

Postby manasi » Tue Jun 22, 2010 10:13 am

Hi,

You can use Full Outer Join on the tables.

e.g.

SELECT A.ID :NULL-IND1,B.ID :NULL-IND2
FROM TABLE1 A FULL OUTER JOIN TABLE2 B
WHERE COND


then in your code you can check for the null indicators.
If NULL-IND1= 0
   IF NULL-IND2=0
       CONTINUE
   ELSE
       INSERT IN T2
   END-IF
ELSE
   IF NULL-IND1= -1
       IF NULL-IND2=0
          DELETE FROM T2
       ELSE
          CONTINUE
       END-IF
    END-IF
END-IF.
Code'd
manasi
 
Posts: 3
Joined: Fri May 21, 2010 10:44 am
Has thanked: 0 time
Been thanked: 0 time

Re: Table Updation using Query

Postby gopi palanisamy » Fri Jun 25, 2010 4:08 pm

@Dick -- I thought it would be feasible for me if we apply single query for insertion instead of multiple Query

@manasi - Thank you Manasi.I will try and let you on how is it going.

Thanks all
gopi palanisamy
 
Posts: 3
Joined: Mon Jun 21, 2010 3:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Table Updation using Query

Postby GuyC » Wed Jun 30, 2010 1:47 pm

suppose col1 is primary key :
insert into tab1 (select * from tab2 B where not exists (select * from tab1 C where c.col1 = b.col1 ))

delete from tab1 C where not exists (select * from tab1 C where c.col1 = b.col1)
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post