Oracle Sql: Update a Table With Data from Another Table

Oracle SQL: Update a table with data from another table

This is called a correlated update

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

Assuming the join results in a key-preserved view, you could also

UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

Update a table with values from another table

MERGE INTO ABC t1
USING (select SID,max(COMP_CODE) COMP_CODE from CC GROUP BY SID) t2
ON (t1.SID1= t2.SID1)
WHEN MATCHED THEN 
UPDATE SET t1.COMP_CODE    = t2.COMP_CODE  

Update from another table Oracle

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 to update table with data from another table

Oracle does not support joins in update queries - unlike other database, such as SQL Server (in which the query you are using would probably run just as it is).

I would recommend a correlated subquery:

update table_b b
set second_value = (select a.second_value from tablea a where a.first_value = b.first_value)
where b.second_value = 0

You might want to add a condition to ensure that only "matching" rows are updated:

update table_b b
set second_value = (select a.second_value from tablea a where a.first_value = b.first_value)
where 
    b.second_value = 0
    and exists (select a.second_value from tablea a where a.first_value = b.first_value)

Oracle : update column depending on column from another table

I think MERGE is the best method to do a multi-table update:

merge into tab1 t1
using tab2 t2
on (
    t1.a = t2.a
    and t1.a between 1 and 10  -- change this as needed
)
when matched then
    update set t1.b = coalesce(t2.d, t1.c);

PL/SQL: I have problem updating a table with data from another table

You basically seem to also updates rows in table roomdb where there is no row in table rentalinvoice and therefore the column rentalbalance will be set to null.

Have a look a following example:

drop table a;
create table a (id number, cost number);
insert into a values (1, 1);
insert into a values (2, 2);

drop table b;
create table b (id number, cost number);
insert into b values (1, 100);

-- updates all rows in a and sets cost to null whenen there is no row in b
update a set cost = (select cost from b where a.id = b.id);
select * from a;

-- only updaes rows in a where there is a row in b
update a set cost = id;
update a set cost = (select cost from b where a.id = b.id) where exists (select 1 from b where a.id = b.id);
select * from a;

---

Related Topics