Update multiple rows using select statement in Oracle

Question

Im using this script for updating "gndt_customer_identification" table, value of V_iden_no coming from select statement.

How i can update multiple row when select return more then one value for V_iden_no ???

Update gndt_customer_identification
set V_iden_code = 'CNIC', 
 V_iden_no = (
 SELECT SUBSTR (V_iden_no,1,13)
 FROM gndt_customer_identification
 WHERE V_iden_code = 'DM_OT'
 AND V_iden_no LIKE '_____________ _%' 
 and N_CUST_REF_NO =10434997
 )
where N_CUST_REF_NO =10434997;

• Littlefoot: It depends on what subquery returns. Is value of SUBSTR(v_iden_no, 1, 13) the same? Then use DISTINCT. If not, which value would you want to get, then? There must be additional condition(s) which further narrow the result set so that only one value is either being returned by that subquery, or being USED from its result set (for example, use MIN or MAX on SUBSTR(v_iden_no, 1, 13)). If that doesn't help, provide some sample data and desired result; explain rules which lead from source to target.

Answer 1

You need in additional correlation - the subquery must select from the matched row only. This will be something similar to

UPDATE gndt_customer_identification AS update_copy
SET V_iden_code = 'CNIC', 
 V_iden_no = (
 SELECT SUBSTR (update_copy.V_iden_no,1,13)
 FROM gndt_customer_identification AS select_copy
 WHERE update_copy.V_iden_code = 'DM_OT'
 AND update_copy.V_iden_no LIKE '_____________ _%' 
-- correlate table copies
 AND update_copy.N_CUST_REF_NO = select_copy.N_CUST_REF_NO
 )
;

The query assumes that the subquery will select not more than ONE row for each definite N_CUST_REF_NO value.