Update statement with corelated subquery: single-row subquery returns more than one row-CodePudding

Environment: Oracle 19

These are the my two tables -

MainTab ( g_year, g_prd, match_col, desc)

MainTab is range partitioned on g_year and sub-paritioned on g_prd

LookupTab ( match_col, desc)

I want to update MainTab's desc column using LookupTab. I am executing below update query on just one sub-partition.

update MainTab a  
SET a.desc = (SELECT distinct b.desc FROM LookupTab b 
                    where a.g_year =2005 and a.g_prd = 1 and a.desc is null 
                            and a.match_col = b.match_col and b.desc is not null )
where a.g_year =2005 and  a.g_prd = 1 and a.desc is null;

This query is throwing ORA-01427 - single-row subquery returns more than one row even though I have distinct clause in subquery.

Question : Why the distinct clause and additional where clause b.desc is not null are not able to prevent multiple rows from being returned in the sub query.

I could fix this by using fetch first 1 row only but I expected distinct clause to work as well.

CodePudding user response：

You have misunderstood DISTINCT. That doesn't limit a result to one row, it just makes sure that every row (the complete one) is DISTINCT within the result and usually there can be many distinct rows.

And as a side note: a.g_year =2005 and a.g_prd = 1 and a.desc is null in the WHERE of the subquery is pointless, it's true whatsoever because it's also the operation in the outer WHERE.

CodePudding user response：

As Sticky Bit says, "Distinct" will return each unique value in that column. Try running this part separately and see if you get more than 1 row returned. I suspect you will:

SELECT distinct b.desc FROM LookupTab b 
                    where a.g_year =2005 and a.g_prd = 1 and a.desc is null 
                            and a.match_col = b.match_col and b.desc is not null

As commented above as well, you don't need the WHERE clause twice. One place or the other is fine.

You will likely need to be more specific with your inner WHERE and remove the distinct part.