Update from another table Oracle

Question

Table1

Tripid  sequence Pattern
  1       1       
  1       2
  1       3
  2       1   
  2       2 

Table2

Tripid Pattern
  1      A
  2      B

I'm trying to update Pattern for table 1, end result should be:

Tripid  sequence Pattern
  1       1        A
  1       2        A
  1       3        A
  2       1        B
  2       2        B

The code that I use:

update table1
set table1.pattern = 
    (select pattern from table2 where table1.tripid = table2.tripid) 
where exists 
    (select pattern from table2 where table1.tripid = table2.tripid)

Oracle Database Error: ORA-01427: single-row subquery returns more than one row

How to do it correctly in oracle 10g?

Answer 1

You can use MERGE statement for this.

Query:

select * from t1

Result:

| TRIPID | SEQ | PATTERN |
|--------|-----|---------|
|      1 |   1 |  (null) |
|      1 |   2 |  (null) |
|      1 |   3 |  (null) |
|      2 |   1 |  (null) |
|      2 |   2 |  (null) |

Query:

merge into t1
using t2
on (t1.tripid = t2.tripid)
when matched then update
set pattern = t2.pattern

Query:

select * from t1

Result:

| TRIPID | SEQ | PATTERN |
|--------|-----|---------|
|      1 |   1 |       A |
|      1 |   2 |       A |
|      1 |   3 |       A |
|      2 |   1 |       B |
|      2 |   2 |       B |