CODEWITHSUNDEEP
sqloracle-databasesql-update
Stanley Mungai
Stanley Mungai

Reputation: 4150

Update one field in a table in reference to another field on the same table Oracle

I need to Update a varchar2 field with sequential values e.g 001, 002, 002 in reference to another field entity_id on the same table. I need it such that if for example I have the same entity_id on two different rows, then the sequential values should be the same.

An example output:

Entity_id      Seq_field
1234           001
1234           001
4567           002
4567           002
3412           003

I have tried with rownum but it gives different values for each entity_id and of course the values do not have the trailing zero. Please help.

Upvotes: 0

Views: 776

Answers (2)

user330315
user330315

Reputation: 

merge into the_table
using
(
  select rowid as rid,
         entity_id, 
         to_char(dense_rank() over (order by entity_id), 'FM00000') as seq
  from foo
) t on (the_table.rowid = t.rid)
when matched 
  then update set seq_field = t.seq;

If you want to start a new sequence for each entity_id you need to change the statement slightly:

merge into foo
using
(
  select rowid as rid,
         entity_id, 
         to_char(row_number() over (partition by entity_id order by null), 'FM00000') as seq
  from foo
) t on (foo.rowid = t.rid)
when matched 
  then update set seq_field = t.seq;

Note that I used row_number() instead of dense_rank() and partition by entity_id to restart the numbering with each new value of entity_id. If you have another column that would determine the "order" for one entity_id, then you can replace the null in order by null with that column e.g. order by created_at

Upvotes: 3

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

How many records do you have? Below is a solution I came up with, but won't work nicely with large set of data.

CREATE TABLE tab (entity_id NUMBER, seq_field VARCHAR2(3));

INSERT INTO tab VALUES (1234, NULL);
INSERT INTO tab VALUES (1234, NULL);
INSERT INTO tab VALUES (4567, NULL);
INSERT INTO tab VALUES (4567, NULL);
INSERT INTO tab VALUES (3412, NULL);

UPDATE tab t SET seq_field = (
  SELECT LPAD(rnk, 3, '0')
    FROM (
      SELECT entity_id, seq_field, DENSE_RANK() OVER (ORDER BY entity_id) AS rnk
        FROM tab
    ) t2
  WHERE t2.entity_id = t.entity_id AND rownum = 1
);

Check at SQLFiddle: http://sqlfiddle.com/#!4/3959d/1

Consider adding both SQL and ORACLE tags to your question, guess you'll get more attention and perhaps a better solution.

Upvotes: 1

Related Questions