Update multiple columns in a TABLE from another TABLE (Oracle)

Question

I would like to update multiple columns in one table based on values in another.

I think I know how to write an update statement in T-SQL that does what I want (haven't tested the below). Problem is I'm trying to translate this for an Oracle database. Does anyone know how to do the following in Oracle:

UPDATE oldauth SET
  AUTHUNIQUENAME=newauth.AUTHUNIQUENAME
  DESCRIPTION=newauth.DESCRIPTION
  MAPPINGAUTHNAME=newauth.MAPPINGAUTHNAME
FROM
 (SELECT * FROM USERS1 WHERE AUTHSOURCEID=100) oldauth
LEFT JOIN
 (SELECT * FROM USERS2 WHERE AUTHSOURCEID=200) newauth
ON 
oldauth.AUTHUSERNAME=newauth.AUTHUSERNAME;

Quassnoi · Accepted Answer · 2009-06-16 14:15:18Z

3

MERGE
INTO   (
       SELECT  *
       FROM    users1
       WHERE   AUTHSOURCEID=100
       ) oldauth
USING  (
       SELECT  *
       FROM    users2
       WHERE   AUTHSOURCEID=200
       ) newauth
ON     oldauth.AUTHUSERNAME=newauth.AUTHUSERNAME
WHEN MATCHED THEN
UPDATE
SET    AUTHUNIQUENAME=newauth.AUTHUNIQUENAME,
       DESCRIPTION=newauth.DESCRIPTION,
       MAPPINGAUTHNAME=newauth.MAPPINGAUTHNAME

answered Jun 16, 2009 at 14:15

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update multiple columns in a TABLE from another TABLE (Oracle)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related