I have a table called Table1:

Fields and values:

id text number
1 a
2 b
1 c

All I want to do is to update the above table, so that the number is set as 1,2,3 i.e, incremented by 1;Hence the resultant table will look as:

id text number
1 a 1
2 b 2
1 c 3

Can you please help me to write an update statement?

Thanks

If I understand you well, you need a sequence:

CREATE SEQUENCE [sequence name]
INCREMENT BY 1
START WITH [where do you wanna start?]
MAXVALUE [an integer, can also be NOMAXVALUE]
MINVALUE [an integer, can also be NOMINVALUE]
CYCLE (or NOCYCLE)

Forgot the update statement

UPDATE [table] SET number = [sequence].nextval;

I tried it on a little test table I created, so I guess it should work for you. My test:

SQL> create table test(a number(2),b varchar2(3),c number(2));

Tabel is aangemaakt.

SQL> insert into test values (1, 'a', null);

1 rij is aangemaakt.

SQL> insert into test values (2, 'b', null);

1 rij is aangemaakt.

SQL> insert into test values(1, 'c', null);

1 rij is aangemaakt.

SQL> create sequence tt
2 increment by 1
3 start with 1;

Reeks is aangemaakt.

SQL> select * from test;

A B C
---------- --- ----------
1 a
2 b
1 c

SQL> update test set c = tt.nextval;

3 rijen zijn bijgewerkt.

SQL> select * from test;

A B C
---------- --- ----------
1 a 1
2 b 2
1 c 3

3 rijen zijn bijgewerkt = 3 rows updated I work with a dutch version