Oracle update syntax

jmpatton
jmpatton used Ask the Experts™
on
I have 2 tables as described below.  Both tables are the exact same structure.  I need to update table2 (columns 3, 4 & 5) with data from table1 based on table1.colum1 = table2.colum1 and table1.colum2 = table2.colum2

Thanks for any help you can provide


Table 1
--------------------
colum1
colum2
colum3
colum4
colum5

Table 2
--------------------
colum1
colum2
colum3
colum4
colum5
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Seems like homework for SQL 101 class.
What have you coded?

HINT: Check out the UPDATE statement.    :p
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
If you have key on column1 and column2 in table1:
update
(select t1.*, t2.column3, t2.column4, t2.column5
  from table1 t1 join table2 t2
  on t1.column1 = t2.column1
  and t1.column2 = t2.column2
)
set t1.column3 = t2.column3,
    t1.column4 = t2.column4,
    t1.column5 = t2.column5

Open in new window

Author

Commented:
@MikeOM_DBA

Hint: im not a DBA or a SQL programmer, that is why I came here looking for help.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
did you try something like this
MERGE
INTO    table2
USING   (
        SELECT  t1.colum3 as newCol3 ,t1.colum4  as newCol4 ,t1.colum5  as newCol5 , t2.rowid AS rid
        FROM    table1 t1
        JOIN    table2 t2
        ON      table1.colum1= table2.colum1 AND table1.colum2= table2.colum2
        )
ON      rowid = rid
WHEN MATCHED THEN
UPDATE
SET     colum3 = newCol3 ,
          colum4 = newCol4 ,
          colum5 = newCol5 

Open in new window


check below link for more info...
http://www.oracle-developer.net/display.php?id=203
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Try this:

update tab2 t2 set (col2,col3,col4) =
(select col2,col3,col4 from tab1 t1 where t1.col1=t2.col1 and t1.col2=t2.col2)
/
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
oops...   mixed up column names...

update tab2 t2 set (col3,col4,col5) =
(select col3,col4,col5 from tab1 t1 where t1.col1=t2.col1 and t1.col2=t2.col2)
/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial