CODEWITHSUNDEEP
sqloracle-databaseoracle11gmerge
Станислав Китаев
Станислав Китаев

Reputation: 147

Merging to table from 2 joined tables

For example I am having two tables with id,age,status and height. And there is a table RESULT which I need to merge to.

Table 1

*id age status*
1  15   1
2  16   1
3  17   0

Table 2

*id height*
1  160
2  170
3  180

And Result table is:

Result table

*id age height*
1  15   160

I need to insert into Result table id,height,age from Table 1 join Table 2 on ID ,where status is 1.

How can I write something like

Merge into Result
USING(Select ... from Table1
join Table2 on Table1.id=Table2.id where status=1)
When Not Matched THEN
Insert into Result VALUES(Table1.id,age,height)

I need to get

RESULT

*id age height*
1   15   160
2   16   170

So how can I implement that merge which will find user with id=2 in Result

Table and Insert and will not Insert user with id=1 because it is already in table?

Upvotes: 0

Views: 54

Answers (2)

Popeye
Popeye

Reputation: 35900

Try this:

MERGE INTO RESULT R USING (
                              SELECT
                                  T1.ID,
                                  T1.AGE,
                                  T1.STATUS,
                                  T2.HEIGHT
                              FROM
                                  TABLE1 T1
                                  JOIN TABLE2 T2 ON T1.ID = T2.ID
                              WHERE
                                  STATUS = 1
                          ) DATAA 
ON ( R.ID = DATAA.ID ) 
WHEN NOT MATCHED 
THEN INSERT (
    ID,
    AGE,
    HEIGHT ) 
    VALUES (
    DATAA.ID,
    DATAA.AGE,
    DATAA.HEIGHT )

Cheers!!

Upvotes: 1

user2042214
user2042214

Reputation: 165

Below is the sql query in need to run whole query together

insert into result
Select t1.Id, t1.Age, t2.Height from Table1 t1 inner join Table2 t2 on t1.Id=t2.Id where t1.status=1

Upvotes: 0

Related Questions