oracle 更新oracle中的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3059633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple rows in oracle
提问by devang
Can you tell me how to update multiple rows in oracle as when I fired the update statement it gave me the following error
你能告诉我如何在 oracle 中更新多行，因为当我触发更新语句时，它给了我以下错误
UPDATE BI_BSELATEST_LATESTPRICESTEST
SET PREVIOUS_DAY_CLOSE =
(SELECT DISTINCT aa.DLYPRICE_CLOSE
FROM DATAFEED_EQTY.FEED_DLYPRICE aa ,
(
SELECT a.sc_code , MAX(a.DLYPRICE_DATE) as max_date
from DATAFEED_EQTY.FEED_DLYPRICE a,BI_BSELATEST_LATESTPRICES b
where a.SC_CODE = b.SC_CODE
and a.ST_EXCHNG = 'BSE'
and a.DLYPRICE_DATE < b.upd_time
group by a.sc_code
) bb
WHERE aa.SC_CODE = bb.sc_code
and aa.DLYPRICE_DATE = max_date)
ORA-01427: single-row subquery returns more than one row
ORA-01427: 单行子查询返回多于一行
Thanks in advance
提前致谢
回答by lc.
A subquery in your statement is wrong. You left off either a WHEREor FIRSTclause, and now it's returning multiple values when it shouldn't.
您语句中的子查询是错误的。您省略了WHEREorFIRST子句，现在它返回了不应该返回的多个值。
You're basically trying to say PREVIOUS_DAY_CLOSEshould be multiple values at the same time. I'm guessing you left off a WHEREclause on your subselect, which would link the results of that subquery to the particular row you're trying to update. Something like (note the bolded line):
您基本上是想说PREVIOUS_DAY_CLOSE应该同时有多个值。我猜您WHERE在子选择中遗漏了一个子句，该子句会将子查询的结果链接到您尝试更新的特定行。类似于（注意粗体）：
UPDATE BI_BSELATEST_LATESTPRICESTEST
SET PREVIOUS_DAY_CLOSE =
(SELECT DISTINCT aa.DLYPRICE_CLOSE
FROM DATAFEED_EQTY.FEED_DLYPRICE aa ,
(
SELECT a.sc_code , MAX(a.DLYPRICE_DATE) as max_date
from DATAFEED_EQTY.FEED_DLYPRICE a,BI_BSELATEST_LATESTPRICES b
where a.SC_CODE = b.SC_CODE
and a.ST_EXCHNG = 'BSE'
and a.DLYPRICE_DATE < b.upd_time
group by a.sc_code
) bb
WHERE aa.SC_CODE = bb.sc_code
and aa.DLYPRICE_DATE = max_date
AND bb.sc_code = BI_BSELATEST_LATESTPRICESTEST.sc_code )Although, I'll be honest, I'm not exactly sure what you're trying to do with this query.
虽然，老实说，我不太确定你想用这个查询做什么。
回答by Allan
I think what you want is this:
我想你想要的是这个：
UPDATE BI_BSELATEST_LATESTPRICESTEST b
SET PREVIOUS_DAY_CLOSE =
(SELECT DISTINCT aa.DLYPRICE_CLOSE
FROM DATAFEED_EQTY.FEED_DLYPRICE aa ,
(
SELECT a.sc_code , MAX(a.DLYPRICE_DATE) as max_date
from DATAFEED_EQTY.FEED_DLYPRICE a
where a.SC_CODE = b.SC_CODE
and a.ST_EXCHNG = 'BSE'
and a.DLYPRICE_DATE < b.upd_time
group by a.sc_code
) bb
WHERE aa.SC_CODE = bb.sc_code
and aa.DLYPRICE_DATE = max_date)
This removes BI_BSELATEST_LATESTPRICESTEST from the sub-query and instead tells the database to use the columns from the table that it is updating to filter the sub-query. As you had written it, the database had no way of knowing how to correlate the columns from the sub-query to the table being updated.
这会从子查询中删除 BI_BSELATEST_LATESTPRICESTEST，而是告诉数据库使用它正在更新的表中的列来过滤子查询。正如您所写的那样，数据库无法知道如何将子查询中的列与正在更新的表相关联。
