0

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'));
Nad
  • 4,605
  • 11
  • 71
  • 160
  • 1
    You set a condition on where to find the *value* of `ne_length` in your subquery, not on which rows to update. – pmdba Jun 14 '22 at 17:28
  • @pmdba: so how can we do it ? – Nad Jun 14 '22 at 18:15
  • are you trying to set `ne_length` to the corresponding `maint_zone_ne_span_length` where the `link_id` values match between the two tables and are in the list of values you gave in the example? – pmdba Jun 14 '22 at 18:19
  • yes, I am trying to that only – Nad Jun 14 '22 at 18:20
  • See if this answers your question: https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle – pmdba Jun 14 '22 at 18:25
  • let me try and update my query accordingly. – Nad Jun 14 '22 at 18:26
  • from the link you posted, I made this query. can I try executing it ? `UPDATE tbl_fiber_inv_cmpapproved_info SET tbl_fiber_inv_cmpapproved_info.ne_length = (SELECT tbl_fiber_inv_jobs.MAINT_ZONE_NE_SPAN_LENGTH FROM tbl_fiber_inv_jobs WHERE tbl_fiber_inv_cmpapproved_info.span_link_id = tbl_fiber_inv_jobs.link_id) WHERE tbl_fiber_inv_cmpapproved_info.span_type='INTRACITY' AND EXISTS (SELECT tbl_fiber_inv_jobs.MAINT_ZONE_NE_SPAN_LENGTH FROM tbl_fiber_inv_jobs WHERE tbl_fiber_inv_cmpapproved_info.span_link_id = tbl_fiber_inv_jobs.link_id);` – Nad Jun 14 '22 at 18:31
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/245609/discussion-between-nad-and-pmdba). – Nad Jun 14 '22 at 18:36
  • You are saying that the update statement in your request updates all rows in the table. I doubt that, but well ... For this to happen there must be no runtime error. If that subquery returned more than one row you'd get "ORA-01427: single-row subquery returns more than one row". As this is not the case, there can be only one or zero rows in TBL_FIBER_INV_JOBS that match `WHERE LINK_ID IN ('MORV_1020','ANND_1017','BBSR_1047','DLHI_5417','MYSR_0104')`. Now, which rows in TBL_FIBER_INV_CMPAPPROVED_INFO do you want to update with that one MAINT_ZONE_NE_SPAN_LENGTH value? – Thorsten Kettner Jun 15 '22 at 12:54
  • @ThorstenKettner: yes, I want to update `NE_LENGTH` column in `TBL_FIBER_INV_CMPAPPROVED_INFO` table – Nad Jun 15 '22 at 14:20
  • Okay, you say that your update statement runs without an error, but it updates all rows. What does the following query return? `SELECT COUNT(*) FROM app_fiberinv.tbl_fiber_inv_jobs WHERE link_id IN ('MORV_1020', 'ANND_1017', 'BBSR_1047', 'DLHI_5417', 'MYSR_0104');` – Thorsten Kettner Jun 15 '22 at 14:46
  • @ThorstenKettner: it returns count as `5` – Nad Jun 15 '22 at 15:43
  • Then there is something wrong. You are selecting all these rows your subquery. This is not allowed in SQL, because you want to update each row with one value, not with five. Two possibilities: Your statement doesn't update all rows, but throws error ORA-01427. Or you are not using Oracle. Which of the two applies? – Thorsten Kettner Jun 15 '22 at 16:09
  • @ThorstenKettner: I am using oracle for updating it. – Nad Jun 15 '22 at 16:12

2 Answers2

1

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');
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

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;
Gnqz
  • 3,292
  • 3
  • 25
  • 35