Update the column value sequentially in oracle db-CodePudding

I need a help in sql query for oracle database .

I am using a imaginary table here to explain the problem.

suppose we have customers table

so this internal counter is based on customer_type (i.e. for each customer type it begins from 1 till the max rows) . while doing testing I have updated the internal_counter to null .

now I want reassign the sequential number to them (for example Business customers) and I am not sure about below query .

    DECLARE
    VAL NUMBER := 1;
BEGIN
    FOR REC IN ( select * from customers where customer_type = 'Business' )
    LOOP
        UPDATE customers SET internal_counter = VAL ;       
        VAL := VAL  1;
    END LOOP;
END;

do you think its the right query ?

I am asking here because I am not db expert and don't want to mess things further .

CodePudding user response：

Don't use PL/SQL and loops; just do it in a single SQL query as it will be quicker and generate far fewer entries into the log files compared to running individual updates for each row:

MERGE INTO customers dst
USING (
  SELECT ROWID AS rid,
         ROWNUM AS rn
  FROM   customers
  WHERE  customer_type = 'Business'
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET internal_counter = src.rn;

However, if you have updated the values to NULL then the first thing to do is to try ROLLBACK and undo any changes you have made that are uncommitted as that may (if you have not used a COMMIT statement or any DDL statements) restore the pervious values.

CodePudding user response：

Use a windowing function like ROW_NUMBER to generate your internal_counter, since it's not a simple sequential counter. Then feed that into an update of the table using the MERGE statement:

merge into customers
using (select rowid as rid,
              ROW_NUMBER() OVER (PARTITION BY customer_type ORDER BY customer_name) internal_counter
         from customers) src
on (customers.rowid = src.rid)
when matched then update set customers.internal_counter = src.internal_counter;