Reputation: 43
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
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
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