SQL error ORA 01427

0
votes

I am trying to update one of the columns in my table by collecting the values from another table in the data store using this query 

UPDATE tablename PT
SET DID = (select distinct(did) from datastore.get_dept_alias
where upper(ltrim(rtrim(deptalias))) = upper(ltrim(rtrim(PT."Dept Descr")))
AND cid = PT.CID)

Note: Both the column names in the table are the same as entered

I get ORA 01427 error. Any idea about the issue? I am trying to understand the other posts of this ORA error

2
sqloracle

2 Answers

2
votes

As you can see here

SQL Error: ORA-01427: single-row subquery returns more than one row

This means that your sub-query 

select distinct(did) from datastore.get_dept_alias
where upper(ltrim(rtrim(deptalias))) = upper(ltrim(rtrim(PT."Dept Descr")))
AND cid = PT.CID)

is returning more than one row.

So, are you sure that distinct (did) is unique? Looks like it's not. I don't recommend using where rownum = 1 because you don't know which one of the values will be used to update; unless you use ORDER BY.

1
votes

Your getting this error because your select statement can return more than one result. You can not update a single cell with a query that can potentially return more than one result.

A common approach to avoid this with many SQL languages is to use a top 1 or something like that to assure the engine that you will only return one result. Note that you have to do this even if you know the query will only return one result. Just because YOU know it doesn't mean that the engine knows it. The engine also has to protect you from future possibilities not just things as they are right this moment.

Update:

I noticed you updated your question to Oracle. So in that case you could limit the subquery to a single result using the where rownum = 1 clause. As other answer pointed out you'd have to use further logic to ensure that top 1 coming back is the right one. If you don't know which one is the right one then solve that first.

The thought also occurs to me that you might be misunderstanding what DISTINCT does. This ensures that the return results are unique - but there could still be multiple unique results.