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
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
MikeOM_DBA

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

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

jmpatton

ASKER
@MikeOM_DBA

Hint: im not a DBA or a SQL programmer, that is why I came here looking for help.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SANDY_SK

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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

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