Update every row with auto-generated value

0
votes

I have a table:

CREATE TABLE "person" (
    "ID" NUMBER(38,0), 
    "NAME" VARCHAR2(50 CHAR)
)

Data:

id name 1 monica 2 null 3 null 4 stephan 5 null

I need update null values with value "person" and auto incremented value (from 1 - person1, person2, person3 etc):

1 monica 
2 person1
3 person2
4 stephan
5 person3

What sql (oracle) query should I use?

3
sqloraclesql-updatedml
Could you please provide more detail about your table structure? ex. provide a CREATE TABLE script that shows us the column names and data types etc. - wweicker
Is there any other column to order this column? - Ankit Bajpai
By the way, not a good idea to name a table "person" (in double quotes). person (without quotes) will be a lot easier to work with. - William Robertson

3 Answers

2
votes

Analytic function is not needed. The update can be done simply like this:

UPDATE person
   SET NAME = 'person' || ROWNUM
 WHERE name IS NULL;

Update

Since the person number needs to be sequential with the IDs of the person, this query can be used:

UPDATE person p
   SET p.name =
              'person'
           || (SELECT person_number
                 FROM (SELECT p2.id, ROW_NUMBER () OVER (ORDER BY p2.id) AS person_number
                         FROM person p2
                        WHERE name IS NULL)
                WHERE id = p.id)
 WHERE p.name IS NULL;
1
votes

You actually have 2 issues, although your question only addresses 1 of them. You must not only 'fix the data' but also 'fix the system'. With the second (unaddressed) being the most important. There are 2 methods for this, lets call them

  1. the correct method
-- update existing data
update person1
   set name = 'PERSON' || to_char(rownum,'FM999') 
 where id is null;

-- fix the system
alter table person modify name not null;
  1. the band-aid method
-- prepare for long term bandaid 
create sequence unknown_person_seq;

-- update existing data
update person
   set name = 'PERSON' || to_char(unknown_person_seq.nextval, 'FM99')
 where name is null;
select * from person2; 

-- adjust the system
alter table person modify name default 'Person' || to_char(unknown_person_seq.nextval, 'FM99');

The difference being the correct method keeps further data corruption out of the system. See demo here. It is far better to raise an exception for invalid data than it is to allow or generate bogus data. Consider your Person table holds customer information and you refer to them as PERSON4. How long will they remain your customer?

0
votes

It works:

SET
    name = (
    SELECT n
    FROM(
        SELECT
            id, 'person' || ROW_NUMBER() OVER (ORDER BY id) AS n
        FROM
            person
                WHERE
            name IS NULL
        )u
    WHERE
        u.id = person.id)
        WHERE
    NAME IS NULL