CODEWITHSUNDEEP
sqloracle-databaseplsqlc#-3.0
vish1990
vish1990

Reputation: 384

Deleating value from auto incremented column

First of all appologies if the question is below standard(basic for most of all).

I have a column whose value gets incremented automatically based on other column.E.g.:if column name has value "abc","abc" ,"xyz","xyz",xyz","hij" the value in auto-incremented column must be "1","2","1","2","3","1".

The problem occurs while deleating or updating record. What if someone delete ["xyz"] value of having value "2"? How to handle such situation ?

Upvotes: 1

Views: 47

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

As one of the options(simple and straightforward one) you can create a view and generate that "auto-incremented column" on the fly - every time you query the view.

Here is an example:

 -- source table, which does not contain that auto incremented
 -- column you are interested in 
create table t1(id, col1) as (
  select 1, 'abc' from dual union all
  select 2, 'abc' from dual union all
  select 3, 'xyz' from dual union all
  select 4, 'xyz' from dual union all
  select 5, 'xyz' from dual union all
  select 6, 'hij' from dual
  );

-- and here is the view
create or replace view t1_v as
  select id
        , col1
        , row_number() over(partition by col1
                                order by id) as auto_inc
   from t1;


select *
  from t1_v;

        ID COL1   AUTO_INC
---------- ---- ----------
         1 abc           1 
         2 abc           2 
         6 hij           1 
         3 xyz           1 
         4 xyz           2 
         5 xyz           3

Updating the value:

-- Update can be issued against base table or 
-- a view, if it's a key-preserved one
update t1  
   set col1 = 'acb'
 where id = 1;

select *
  from t1_v;


        ID COL1   AUTO_INC
---------- ---- ----------
         2 abc           1 
         1 acb           1 
         6 hij           1 
         3 xyz           1 
         4 xyz           2 
         5 xyz           3

Deleting a row:

-- You can delete from the base table or 
-- a view, if it's a key-preserved one
delete from t1  
  where id = 4; 

select *
  from t1_v;

        ID COL1   AUTO_INC
---------- ---- ----------
         2 abc           1 
         1 acb           1 
         6 hij           1 
         3 xyz           1 
         5 xyz           2

Upvotes: 1

Related Questions