CODEWITHSUNDEEP
sqloracle-databasemergeoracle11g
Pavan Kumar
Pavan Kumar

Reputation: 33

Merge statement help in oracle

I am using merge for the 1 st time ... I went through existing questions but couldn't get proper help.

Please help me with the below need,

I have a table "table_a" with 3 columns A, B and C. C is a new column added combination of column A and B are unique, to be specific column B is a list of sub codes taken from table_b and configured against the entity in column A.

I need to update column C with a hard coded value for the existing A and B combinations and if some subcode missing from table_b in table_a I need to insert the rows for the same in table_a.

eg. table_a 

A    B   C                              
=    =   =
p    x
p    y

table_b

M
=
x
y
z

After execution of query

table_a

A    B   C                              
=    =   =
p    x   1   -- updated with column C
p    y   1   -- updated with column C
p    z   1   -- new row inserted for the row in table_b

Kindly let me know if anything is not clear.

Upvotes: 2

Views: 3317

Answers (2)

Alex Poole
Alex Poole

Reputation: 191245

If you don't want to hard-code the inserted values you can use an in-line view to generate all the expected combinations and merge against that.

merge into table_a a
using (
  select t.a, b.m, 1 as c
  from (select distinct a from table_a) t
  cross join table_b b
) b
on (a.b = b.m)
when matched then
  update set a.c = b.c
when not matched then
  insert (a, b, c) values (b.a, b.m, b.c);

The using clause does a cross-join of all (distinct) table_a.a values against all table_b.m values, to give all possible combinations. (Which seems to be what you want; you haven't shown any other link between the two). I've also included the fixed value 1 in that view.

Then the merge either sets c for the rows the matching values that already exit in table_a, or inserts a new row using the values from the in-line view.

SQL Fiddle.

You might be able to get your unique a values from some other look-up table, which would be better than hitting table_a twice, but that depends on your real data model.

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

MERGE INTO table_A a
   USING table_b b
      ON(a.b = b.m)
   WHEN MATCHED THEN 
      UPDATE SET a.c = 1
   WHEN NOT MATCHED THEN 
      INSERT (a, b, c)
      VALUES ('P', b.m, 1)

Note : The insert has two hardcode values for column a and column c as 'P' and 1 respectively.

Upvotes: 2

Related Questions