CODEWITHSUNDEEP
sqloracle-databasesql-update
Yigitalp Ertem
Yigitalp Ertem

Reputation: 2039

Consecutively update values starting from a number in the table SQL

I have a question regarding the usage of UPDATE statement. Is there a way for me to code the following scenario with only SQL instead of writing a stored procedure?

I tried to simplify the case.

myTable has 4 columns and its values are as follows:

COMP1   COMP2   NO  ACTIVE
0       0       4   N
4       1           Y
2       2       21  Y
3       1       1   Y
1       3       43  Y
2       1           Y
3       1       12  Y
2       2       0   Y
3       2           Y
1       1       5   N

So that, after the UPDATE statement is executed, myTable will be as follows (if we order it)

COMP1   COMP2   NO  ACTIVE
0       0       4   N
1       1       5   N
1       3       6   Y
2       1       7   Y
2       2       8   Y
2       2       8   Y
3       1       9   Y
3       1       9   Y
3       2       10  Y
4       1       11  Y

I'm wondering if I would be able to do it without the need to select the whole list and looping in a CURSOR in a stored procedure.

(Update: I've written the SP. A simplified version is as follows. It may have problems since I changed all the names and deleted many conditions.)

 PROCEDURE updateAllNo
   IS
      CURSOR c1
      IS
           SELECT *
             FROM MyTable SE
            WHERE SE.ACTIVE = 'Y'
         ORDER BY SE.COMP1, SE.COMP2;

      v_last_no    NUMBER := 0;
      v_last_comp2   DATE := SYSDATE + 100;
      v_last_comp1   DATE := SYSDATE + 100;
      v_now_comp2    DATE := SYSDATE;
      v_now_comp1    DATE := SYSDATE;
   BEGIN
     SELECT MAX (SE.NO)
       INTO v_last_no
       FROM MyTable SE
      WHERE SE.ACTIVE = 'N';

     SELECT MAX (SE.COMP1), MAX (SE.COMP2)
       INTO v_last_comp1, v_last_comp2
       FROM MyTable SE
      WHERE     SE.ISLEMBASARILI = 'E'
            AND SE.NO = v_last_no;

     FOR r1 IN c1
     LOOP
        BEGIN
           v_now_comp2 := r1.COMP2;
           v_now_comp1 := r1.COMP1;

           IF v_now_comp2 != v_last_comp2 OR v_now_comp1 != v_last_comp1
           THEN
              v_last_no := v_last_no + 1;
           END IF;

           UPDATE MyTable SE
              SET SE.NO = v_last_no
            WHERE SEQ_ID = r1.seq_id;

           v_last_comp2 := v_now_comp2;
           v_last_comp1 := v_now_comp1;
        END;
     END LOOP;

     COMMIT;

   END;

I'm using Oracle 11g.

Upvotes: 1

Views: 212

Answers (2)

CodeNewbie
CodeNewbie

Reputation: 2091

I am assuming that the field NO is always null when ACTIVE='Y'. If this is the case, then MAX(NO) will return the same value whether or not the WHERE ACTIVE = 'N' condition is specified. With this assumption, this UPDATE statement should do the trick.

UPDATE myTable 
SET NO = (SELECT MAX(NO) FROM myTable) + 1
WHERE ROWID IN 
(SELECT ROWID FROM myTable
WHERE ACTIVE = 'Y'
ORDER BY COMP1, COMP2);

Upvotes: 0

user330315
user330315

Reputation:

The new number can be retrieved using a window function:

The following will retrieve the active rows and calculate the new value for no

select comp1, 
       comp2,
       row_number() over (order by comp1, comp2) + (select max(no) from mytable where active = 'N') as rn
from mytable
where active = 'Y'

This can now be used in a MERGE statement to run an update against the table. As the table apparently has no PK, I will use the ROWID to match the rows:

merge into mytable tg
using (
    select rowid as rid,
           comp1, 
           comp2,
           row_number() over (order by comp1, comp2) + (select max(no) from mytable where active = 'N') as rn
    from mytable
    where active = 'Y'
) t on (t.rid = tg.rowid)
when matched then update
   set no = t.rn;

This will most definitely be faster than a loop with single row updates - especially for larger tables.

Upvotes: 1

Related Questions