How to only update part of a column based on conditions

oracleupdate

This is a continuation of Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row" , however i felt the question was too different to follow up in comments

So when i run the query:

update table1
set column1 = (SELECT distinct color
                FROM table2
                Where table1.id = table2.id 
                and rownum = 1
              )

Everything where the conditions above does not apply returns null, however i would like to keep the value of the fields who do not get a new color

The create table:

CREATE TABLE table1
id varchar2 (20) [NULL]
color varchar2 (20) [NULL]

CREATE TABLE table2
id varchar2 (20) [NULL]
color varchar2 (20) [NULL]

where for example table 1 has 2 rows that are:

id: 123, color: blue
id: 124, color: red

and table 2 has 2 rows that are:

id: 124, color: green
id: 125, color: brown

i would then like to update table1 with table 2 so that table one has the 2 rows:

id: 123, color: blue
id: 124, color: green

Best Answer

If you don't want to use MERGE like @flashsplat suggests, you must reuse the set expression in the WHERE clause:

update table1
    set column1 = (SELECT distinct color
                   FROM table2
                   Where table1.id = table2.id 
                     and rownum = 1
                  )
WHERE EXISTS (
    select 1 from table2
    Where table1.id = table2.id
)

I.e. only do the update when there is something to update

Related Question