2

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?

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.

  • 1
    Note that the subquery would still return multiple rows for any id with more than one distinct color involved, which we know is true from the question. It would fix all instances where there are multiple rows, but only one distinct color. – RDFozz Jul 26 '18 at 16:08
  • Arhh yes i've seen other mentions of rownum = 1, but even after reading up on it I've yet to understand it fully, will this then still check the duplicates and give them an appropriate color, or will this skip the duplicates? I know it sounds weird, but when running the query i got an unexpected result, which made me wonder if i misunderstood rownum = 1 – Levicia Jul 27 '18 at 7:51
  • I did not notice the distinct part till now due to my blindness, i think i'm starting to get it now, thanks for the help! – Levicia Jul 27 '18 at 7:54
  • No problem; again, the rownum = 1 will return the just the first distinct color, not necessarily the most common color.... for that you would need another nested query or maybe an analytical function. Too busy to test that right now! Oh, and usually when using rownum with a value other than 1, you want a rownum <= 6 for example to get just 6 rows. If you do a rownum = 6 to get the sixth row, it will return zero rows due to when rownum is generated. – Mark Stewart Jul 27 '18 at 15:46

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