SQL错误ORA 01427 [英] SQL error ORA 01427

问题描述

我正在尝试通过使用此查询从数据存储中另一个表中收集值来更新表中的列之一

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

我收到ORA 01427错误.对这个问题有什么想法吗? 我正在尝试了解此ORA错误的其他帖子

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

推荐答案

您可以看到

SQL错误:ORA-01427:单行子查询返回多个行

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

这意味着您的子查询

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

返回不止一行.

那么，您确定distinct (did)是唯一的吗?看起来不是.我不建议使用where rownum = 1，因为您不知道将使用哪个值来更新.除非您使用ORDER BY.

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.

这篇关于SQL错误ORA 01427的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！