更新oracle中的多行

发布于 2024-09-05 15:59:38 字数 701 浏览 3 评论 0原文

你能告诉我如何更新 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: 单行子查询返回多行

提前致谢

原文

Can you tell me how to update multiple rows in oracle as when I fired the update statement it gave me the following error

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

Thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沩ん囻菔务 2024-09-12 15:59:38

您的语句中的子查询是错误的。您遗漏了 WHERE 或 FIRST 子句，现在它在不应该返回的情况下返回了多个值。

您基本上是想说 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

不过，老实说，我不太确定您想对这个查询做什么。

A subquery in your statement is wrong. You left off either a WHERE or FIRST clause, and now it's returning multiple values when it shouldn't.

You're basically trying to say PREVIOUS_DAY_CLOSE should be multiple values at the same time. I'm guessing you left off a WHERE clause 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):

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.

回复收藏 0 原文

丑疤怪 2024-09-12 15:59:38

我认为您想要的是这样的：

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)

这将从子查询中删除 BI_BSELATEST_LATESTPRICESTEST ，并告诉数据库使用它正在更新的表中的列来过滤子查询。正如您所写的，数据库无法知道如何将子查询中的列与正在更新的表相关联。

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.

回复收藏 0 原文

~没有更多了~