Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row" [closed]

Question

I'm currently making SQL that'll update data from 1 table, based on data from another table for my company

update table1
set column1 = (SELECT color
                FROM table2
                Where table1.id = table2.id)

My goal is to make table1's column 1 to consist of the colors from table 2, however the id is not unique, instead it's cvr of the companies, therefore they appear multiple times giving me the error "single-row subquery returns more than one row". The duplicates can have different colors however, making me unsure on how to tackle this, any suggestions?

Also it would seem that while duplicates can be differnet colors, in 99% of all instances they don't, so if the first isn't possible, would there be a way for me to update, so all duplicates have same color?

Answer 1

The set column1 = clause requires a single value as the error message indicates. Sounds like you have a data model problem, but if you need a quick fix, your select query can be modified to return a single value; the following a unique color from table2 if more than one color matches your where clause:

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

If I had more time, I'd add another level to find the most common color that matched, and returned the first row.

Updated subquery to return first distinct color - thanks to RDFozz for pointing this out.