1

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
  • The example is an extremely simplified version, while trying to get the point across, as the company has confidential information im not allowed to reveal, however above would be my best attempt at describing what i'm trying to do – Levicia Jul 27 '18 at 8:53
  • There is no rownum field in the table2 table structure. – Akina Jul 27 '18 at 9:17
  • Do the table have UNIQUE or PRIMARY KEY constraints? Simplified or not, add the crucial details in the question. Otherwise people have to guess or ask questions like "Can there be two rows with same id and different colour in table2? What should happen then?" – ypercubeᵀᴹ Jul 27 '18 at 10:04
  • Or "Can there be two rows with same id and same colour in table2? What should happen then?" – ypercubeᵀᴹ Jul 27 '18 at 10:04
  • 1
    @Akina, rownum is an Oracle thing. It is a hidden enumeration of the result. – Lennart Jul 27 '18 at 13:46
3

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

3

I don't fully understand the question, but I believe what you're looking for is a MERGE statement. A rough example from memory:

MERGE INTO table1 t1
USING table2 t2
ON (t1.id=t2.id)
WHEN MATCHED THEN UPDATE SET
    t1.color = t2.color
WHEN NOT MATCHED THEN INSERT
    (t1.ID, t1.color) VALUES (t2.ID, t2.color);

If you don't want to override the colors, you can simply remove the 'WHEN MATCHED' part.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.