How to update a table from a another table

  • I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2)

    Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. So how to implement that ? BTW I am using oracle


    Do both the tables contain same number of records?

  • An alternative approach to the co-related subquery (suggested by Kerri) would be to use the MERGE statement, which might be more efficient than the subselect (which can only be verified by looking at the execution plan of both statements).

    MERGE INTO table_b 
      SELECT id,
      FROM table_a
    ) ta ON ( =
        SET table_b.field_2 = ta.field_2

    The only restriction to the `MERGE` statement is that you cannot update the column on which it is joined, i.e. you cannot update the column used in the `ON` clause.

    This worked for me, updated 2.5 million rows vs. attempting the sub-query method which ran for maybe 45 minutes before erroring with `ORA-01555: snapshot too old`

  • Not exactly sure what you're after on this one, but this should work as a one-off, or continuous via a scheduled job:

    UPDATE table_a a
       SET field_2 = ( SELECT field_2
                         FROM table_b b
                        WHERE = )

    Now, each time the above is executed, it will do it across all rows in the table. If this is something you need to do all the time, I would suggest something else, but for a one-off or very small tables it should be sufficient.

  • I have done this successfully using one table in user1 from another table in user2:

    update user1.table1 a
    set a.field1 = (
      select b.field1
      from user2.table2 b
      where a.field1=b.field1
    where <condition for update user1.table1>
  • You could create a trigger on tableB that updates tableA every time field_2 on tableB is updated. Check here for more info on creating triggers -

    Not relevant answer

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM