Link to home
Avatar of jmpatton
jmpatton

asked on

Oracle update syntax

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
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

Seems like homework for SQL 101 class.
What have you coded?

HINT: Check out the UPDATE statement.    :p
Avatar of Qlemo
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

Avatar of jmpatton
jmpatton

ASKER

@MikeOM_DBA

Hint: im not a DBA or a SQL programmer, that is why I came here looking for help.
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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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)
/