hross hross - 2 months ago 7
SQL Question

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

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;

Answer
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