Update query is working on all rows even after selecting the particular values in oracle-CodePudding

I have written a query where I want to just update some of the LINK_ID. But it is updating all the rows in that table.

Here is my query

UPDATE APP_FIBERINV.TBL_FIBER_INV_CMPAPPROVED_INFO 
 SET NE_LENGTH = 
(select MAINT_ZONE_NE_SPAN_LENGTH from APP_FIBERINV.TBL_FIBER_INV_JOBS  WHERE LINK_ID IN ('MORV_1020','ANND_1017','BBSR_1047','DLHI_5417','MYSR_0104'));

CodePudding user response：

Assuming both tables share the LINK_ID as primary and foreign key, you could just use a MERGE:

MERGE INTO APP_FIBERINV.TBL_FIBER_INV_CMPAPPROVED_INFO APPR_NFO
USING (
        SELECT LINK_ID, MAINT_ZONE_NE_SPAN_LENGTH
          FROM APP_FIBERINV.TBL_FIBER_INV_JOBS 
         WHERE LINK_ID IN ('MORV_1020','ANND_1017','BBSR_1047','DLHI_5417','MYSR_0104')
      ) INV_JOBS
   ON ( APPR_NFO.SPAN_LINK_ID = INV_JOBS.LINK_ID)
 WHEN MATCHED THEN UPDATE SET APPR_NFO.NE_LENGTH  = INV_JOBS.MAINT_ZONE_NE_SPAN_LENGTH;

CodePudding user response：

I still doubt that the update statement you have posted updates all rows in the table. It must throw an error

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

instead, because your subquery returns five rows where it must be one, as you must find one value for each row you want to update.

This means your subquery is wrong. It selects five rows, where it must select one. You don't want to find the five values for 'MORV_1020', 'ANND_1017', but the one value for the link ID of the row you are updating.

You also want to update certain rows (those with the five link IDs), so you must add a WHERE clause at the end of your update statement.

UPDATE app_fiberinv.tbl_fiber_inv_cmpapproved_info i
 SET ne_length = 
 (
    SELECT j.maint_zone_ne_span_length
    FROM app_fiberinv.tbl_fiber_inv_jobs j
    WHERE j.link_id = i.span_link_id
 )
WHERE span_link_id IN ('MORV_1020', 'ANND_1017', 'BBSR_1047', 'DLHI_5417', 'MYSR_0104');