While updating getting no data found error. Need to update the record to 0 if any one the rows for t-CodePudding

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 |
 ------ ------ ------------

CodePudding user response：

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>

CodePudding user response：

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.

CodePudding user response：

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;