CODEWITHSUNDEEP
databaseoracle-database
Mirza Bilal
Mirza Bilal

Reputation: 551

Auto Increment Values in Oracle Column

I have a table in my oracle DB which has a column with random values. Screenshot is attached below : enter image description here

I had manually updated the first row to "V0001". Is there any way I can update the rest of the rows to "V0002", "V0003" and so on without manual intervention.

Upvotes: 0

Views: 162

Answers (2)

stefan
stefan

Reputation: 2252

You could use a sequence for this. Create a sequence, convert the sequence's .NEXTVAL to a string, use CONCAT() and UPDATE eg:

Table

create table demo
as
select dbms_random.string( 'x', 11 ) as vehicleid
from dual
connect by level <= 100 ;

select * from demo fetch first 10 rows only ;
-- output
VEHICLEID
LS23XFRNH5N
47DUDNOIRO9
POS5GQSQLMO
BBEEZJMQZI4
2Q8QE30HM2E
S7M5V40YNTD
N2X1YN0OIE3
...

Sequence

create sequence vehicleid_seq start with 1 increment by 1 ;

Update

update demo
set vehicleid = concat( 'V', to_char( vehicleid_seq.nextval, 'FM00000' ) ) ;

Result

select * from demo order by vehicleid fetch first 10 rows only ;

VEHICLEID
V00001
V00002
V00003
V00004
V00005
V00006
V00007
V00008
V00009
V00010

dbfiddle

Upvotes: 1

Lilibeth
Lilibeth

Reputation: 111

The identifier code of a table is recommended to be a numeric data, what you could do is an extra field that works as a second code, perhaps called secondary_code. You can do it with Stored Procedure, I give you a small example:

DELIMITER$$
DROP PROCEDURE IF EXISTS sp_genwrar_code$$
CREATE PROCEDURE sp_genwrar_code(
OUT p_secondary_code VARCHAR(4) 
)
BEGIN
    DECLARE accountant INT;
    BEGIN
        SET accountant = (SELECT COUNT(*)+1 FROM product); 
        IF(accountant <10)THEN
            SET p_secondary_code= CONCAT('V00',accountant );
            ELSE IF(accountant<100) THEN
                SET p_secondary_code= CONCAT('V0',accountant);
                ELSE IF(accountant<1000)THEN
                    SET p_secondary_code= CONCAT('V',accountant );
                END IF;
            END IF;
        END IF; 
    END;
END$$

With that you can generate codes as you need with the structure 'V001'

Upvotes: 0

Related Questions