While updating getting no data found error. Need to update the record to 0 if any one the rows for that ID is 0

user_17266973 0
Viewed 38
CREATE TABLE test_tab (
    s_id        NUMBER(10),
    e_id        NUMBER(10),
    active_flg  NUMBER(1)
);

INSERT INTO test_tab VALUES(1,11,1);
INSERT INTO test_tab VALUES(2,11,1);
INSERT INTO test_tab VALUES(3,11,0);
INSERT INTO test_tab VALUES(4,12,1);
INSERT INTO test_tab VALUES(5,12,1);

COMMIT;

Tool Used: SQL Developer(18c)

I want to update the active_flg column by identifying the value 0 in it. Suppose, for e_id 11 we have 3 rows so first it should check if for e_id 11 is there any 0 active_flg if 0 exists for that particular e_id then it should update entire rows for e_id 11 to 0. If there are no entries for active_flg then it should not update anything.

My Attempt:

SET SERVEROUTPUT ON;
DECLARE
lv_row test_tab%ROWTYPE;
BEGIN
FOR i IN (SELECT * FROM test_tab)
LOOP
SELECT * INTO lv_row FROM test_tab WHERE e_id = i.e_id AND active_flg = 0;
UPDATE test_tab SET active_flg = 0 WHERE active_flg = 0;
END LOOP;
END;

But I am getting no data found error.

Expected output:

+------+------+------------+
| s_id | e_id | active_flg |
+------+------+------------+
|    1 |   11 |          0 |
|    2 |   11 |          0 |
|    3 |   11 |          0 |
|    4 |   12 |          1 |
|    5 |   12 |          1 |
+------+------+------------+
plsql oracle
3 Answers

How about merge (instead of PL/SQL)?

Before:

SQL> select * From test_tab order by s_id;

      S_ID       E_ID ACTIVE_FLG
---------- ---------- ----------
         1         11          1
         2         11          1
         3         11          0
         4         12          1
         5         12          1

Merge:

SQL> merge into test_tab a
  2    using (select e_id from test_Tab
  3           where active_flg = 0
  4          ) b
  5    on (a.e_id = b.e_id)
  6    when matched then update set
  7      a.active_flg = 0;

3 rows merged.

After:

SQL> select * From test_tab order by s_id;

      S_ID       E_ID ACTIVE_FLG
---------- ---------- ----------
         1         11          0
         2         11          0
         3         11          0
         4         12          1
         5         12          1

SQL>
user_90979060

One (practical and fast) option is to use analytical function presuming active_flg has two values(0 and 1 only)

SQL> CREATE TABLE test_tab2 AS

SQL> SELECT t.s_id, t.e_id, MIN(t.active_flg) OVER (PARTITION BY t.e_id) AS active_flg
       FROM test_tab t;

SQL> DROP TABLE test_tab;

SQL> ALTER TABLE test_tab2 RENAME TO test_tab;

but if the table has indexes, then they all should be recreated; and if there are privileges to be granted to the other users, they also should be regranted.

user_58413060

The reason the no data found is raised is that the SELECT INTO clause does not have data for each value of i.e_id. Some records have active_flg = 1 so it will raise a no data found for that record.

DECLARE
BEGIN
  -- select DISTINCT so there is only 1 row per e_id.
  -- add WHERE clause to only select e_id values that 
  --  have an active_flag = 0. since no action is to be take on the 
  --  others ignore those
  FOR i IN (SELECT DISTINCT e_id FROM test_tab WHERE active_flag = 0)
  LOOP
    -- no need for the select into. The resultset of the cursor for loop
    -- only contains the relevant records.
    UPDATE test_tab SET active_flg = 0 WHERE e_id = i.e_id;
  END LOOP;
END;
user_41898140
Related