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?

score 3 · Accepted Answer · answered Dec 10 '15 at 05:10

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 |

how come update from another table doesn't work well in oracle?? — Sailormoon, Dec 10 '15 at 21:47

score 0 · Answer 2 · answered Dec 21 '15 at 16:02

Maybe, tripid is not unique in table2?

It is in your example, but is it unique in your real data?

DROP  table table1;
create table table1(tripid number, sequence number, pattern  CHAR(1));
insert into table1 values (1,1,null);
insert into table1 values (1,2,null);
insert into table1 values (1,3,null);
insert into table1 values (2,1,null);
insert into table1 values (2,2,null);

DROP  table table2;
create table table2(tripid number, pattern CHAR(1));
insert into table2 values (1,'A');
insert into table2 values (2,'B');

commit;

select table2.tripid, table2.pattern from table2,table1 where table1.tripid = table2.tripid;


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);

commit;

select * from table1;

returns:

Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Table dropped.
Table created.
1 row created.
1 row created.
Commit complete.

    TRIPID PATTERN
---------- -------
         1 A      
         1 A      
         1 A      
         2 B      
         2 B      

5 rows selected.
5 rows updated.


    TRIPID   SEQUENCE PATTERN
---------- ---------- -------
         1          1 A      
         1          2 A      
         1          3 A      
         2          1 B      
         2          2 B      

5 rows selected.

Which proves that with good data, Oracle (and your query) works perfectly.

Christian

Update from another table Oracle

2 Answers2