Oracle Update Statement

Star79
Star79 used Ask the Experts™
on
Oracle Update Statement.
Iam trying to update a column in a table

update SC_SERIAL_NO set po_line_id = (

select CL.C_ORDERLINE_ID
 
   

from sc_serial_no sc

left JOIN M_ATTRIBUTESETINSTANCE ASI ON sc.LOT_NAME = ASI.LOT --and sc.ad_org_id = asi.ad_org_id
left JOIN M_TRANSACTION MT ON ASI.M_ATTRIBUTESETINSTANCE_ID = MT.M_ATTRIBUTESETINSTANCE_ID
left JOIN M_INOUTLINE ML ON MT.M_INOUTLINE_ID = ML.M_INOUTLINE_ID and MT.M_PRODUCT_ID = ML.M_PRODUCT_ID AND MT.AD_ORG_ID = ML.AD_ORG_ID
left JOIN C_ORDERLINE CL ON ML.C_ORDERLINE_ID = CL.C_ORDERLINE_ID
left JOIN C_ORDER C ON CL.C_ORDER_ID = C.C_ORDER_ID


WHERE  
MT.MOVEMENTTYPE ='V+'
)
The above is erroring out with :
"single-row subquery returns more than one row"
Please help me on how to do an update on a column
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
We really can't help rewrite the statement because we don't have your tables or data.

The error means the query you are executing returns, well, more than one row.  So the update doesn't know what rows value to assign to that column.

When I see updates like that with an entire query inline, I think MERGE:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F

There are many examples on the web.

MERGE can have issues when updating the same table you are querying from.

If you can explain more about the columns involved, we can try to offer more advice.
Mark GeerlingsDatabase Administrator

Commented:
Oracle supports using a sub-query to provide the value for an update, but the sub-query must be written so it returns exactly one row for each record that you intend to update.  It is hard for us to write that sub-query without seeing or knowing your data.

One quick possible workaround is to add a group operator (like: min, max, avg, etc.) on the value you are selecting.  That guarantees that at most one row will be returned by the subquery, so that will avoid the "single-row subquery..." error.  But will that value be correct with your data?  We cannot answer that.  It is usually better to add enough "where" clause conditions to insure that only the intended value gets returned.
Mark GeerlingsDatabase Administrator

Commented:
I'm a bit concerned by the "left joins" in your sub-query.  "Left joins" are usually used only when a "normal" (or "inner") join will not return a row, because there is no record in the related table.  But, if there is actually no related record, are you sure that your sub-query will return a correct value?  Or if there is a related record, why the "left join"?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

SharathData Engineer

Commented:
try this.
update (
select sc.po_line_id, CL.C_ORDERLINE_ID
from sc_serial_no sc
left JOIN M_ATTRIBUTESETINSTANCE ASI ON sc.LOT_NAME = ASI.LOT --and sc.ad_org_id = asi.ad_org_id
left JOIN M_TRANSACTION MT ON ASI.M_ATTRIBUTESETINSTANCE_ID = MT.M_ATTRIBUTESETINSTANCE_ID
left JOIN M_INOUTLINE ML ON MT.M_INOUTLINE_ID = ML.M_INOUTLINE_ID and MT.M_PRODUCT_ID = ML.M_PRODUCT_ID AND MT.AD_ORG_ID = ML.AD_ORG_ID
left JOIN C_ORDERLINE CL ON ML.C_ORDERLINE_ID = CL.C_ORDERLINE_ID
left JOIN C_ORDER C ON CL.C_ORDER_ID = C.C_ORDER_ID
WHERE  
MT.MOVEMENTTYPE ='V+'
) t1
set t1.po_line_id = t1.C_ORDERLINE_ID;

Open in new window

Author

Commented:
I tried Sharath comment but it retunred me with error:
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.
Mark GeerlingsDatabase Administrator

Commented:
Your update statement includes a "where" clause in the sub-query, but no "where" clause for the update itself.  That means every row in the table will be updated.  Is that what you want?  If this is a one-time task that may be OK.  But, if this is something that will need to be done repeatedly, you will need a "where" clause for the update also, so records that were already updated don't get updated again (unless you actually want them to be updated again).

Try to write a sub-query that returns exactly one row for each record that you intend to update.  You can test the sub-query first with a query of  SC_SERIAL_NO that returns the records you intend to update.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial