CODEWITHSUNDEEP
sqloracle-database
Steve Wold
Steve Wold

Reputation: 43

newbie oracle update statement error

oracle query:

UPDATE AIRMODEL_NETWORK_SUMMARY 
SET CASES_PRODUCED = (SELECT DISTINCT PRDCTN_RUN_ACTL_CASE_QTY
                       FROM AIRMODEL_NETWORK_SUMMARY, HISTORY_PRODUCTION, PERIOD_TO_PROCESS
                      WHERE AIRMODEL_NETWORK_SUMMARY.FSCL_WK_IN_YR_NUM = HISTORY_PRODUCTION.FSCL_WK_IN_YR_NUM 
                        AND AIRMODEL_NETWORK_SUMMARY.FSCL_YR_NUM = HISTORY_PRODUCTION.FSCL_YR_NUM 
                        AND AIRMODEL_NETWORK_SUMMARY.LOC_ID = HISTORY_PRODUCTION.LOC_ID 
                        AND HISTORY_PRODUCTION.FSCL_WK_IN_YR_NUM = PERIOD_TO_PROCESS.FSCL_WK_IN_YR_NUM 
                        AND HISTORY_PRODUCTION.FSCL_YR_NUM = PERIOD_TO_PROCESS.FSCL_YR_NUM);

is returning the following error:

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

Another attempt at correction:

 MERGE INTO AIRMODEL_NETWORK_SUMMARY AIRMODEL_NETWORK_SUMMARY1 
  USING (SELECT DISTINCT PRDCTN_RUN_ACTL_CASE_QTY, 
           AIRMODEL_NETWORK_SUMMARY2.rowid AS r 
      FROM AIRMODEL_NETWORK_SUMMARY AIRMODEL_NETWORK_SUMMARY2 
           INNER JOIN HISTORY_PRODUCTION 
              ON AIRMODEL_NETWORK_SUMMARY2.FSCL_WK_IN_YR_NUM = HISTORY_PRODUCTION.FSCL_WK_IN_YR_NUM 
                 AND AIRMODEL_NETWORK_SUMMARY2.FSCL_YR_NUM = HISTORY_PRODUCTION.FSCL_YR_NUM 
                 AND AIRMODEL_NETWORK_SUMMARY2.LOC_ID = HISTORY_PRODUCTION.LOC_ID 
           INNER JOIN PERIOD_TO_PROCESS 
              ON HISTORY_PRODUCTION.FSCL_WK_IN_YR_NUM = PERIOD_TO_PROCESS.FSCL_WK_IN_YR_NUM 
                 AND HISTORY_PRODUCTION.FSCL_YR_NUM = PERIOD_TO_PROCESS.FSCL_YR_NUM) 
   ON (AIRMODEL_NETWORK_SUMMARY1.rowid = r) 
 WHEN MATCHED THEN UPDATE 
  SET CASES_PRODUCED = PRDCTN_RUN_ACTL_CASE_QTY;

returns the following error:

ORA-30926: unable to get a stable set of rows in the source tables

I am a noob and need help :(

thank you.

Upvotes: 0

Views: 50

Answers (1)

mrjoltcola
mrjoltcola

Reputation: 20862

You can assign only a single, scalar value to CASES_PRODUCED. So your subquery needs to produce only a single, scalar value.

You need to find why your subquery returns more than one row.

SELECT DISTINCT ... will select unique rows, not the same as ONE ROW.

Do you need the sum total of all the PRDCTN_RUN_ACTL_CASE_QTY in the rows?

Or the average?

Or the MAX number?

Or the first row?

Use an aggregate function if so:

Total

UPDATE AIRMODEL_NETWORK_SUMMARY 
    SET CASES_PRODUCED = (SELECT SUM(PRDCTN_RUN_ACTL_CASE_QTY) ...

Max

UPDATE AIRMODEL_NETWORK_SUMMARY 
    SET CASES_PRODUCED = (SELECT MAX(PRDCTN_RUN_ACTL_CASE_QTY) ...

Average

UPDATE AIRMODEL_NETWORK_SUMMARY 
    SET CASES_PRODUCED = (SELECT AVG(PRDCTN_RUN_ACTL_CASE_QTY) ...

First row

UPDATE AIRMODEL_NETWORK_SUMMARY 
    SET CASES_PRODUCED = (SELECT PRDCTN_RUN_ACTL_CASE_QTY ... 
                          FROM ... 
                          WHERE ROWNUM = 1)

It is critical, especially when learning, and struggling with syntax, that you clearly understand

  1. What you need from the query
  2. What your query is returning

Just trying a bunch of alternatives until you get success is a sure fire way to create erroneous data that seems to work.

My suggestion is to first copy the subquery SELECT to a different window and run it, view and understand the results. It should be clear that it is a multi-row result. Work with the subquery until it is returning a correct, single result, then plug it back into the larger update.

Upvotes: 2

Related Questions