Results 1 to 3 of 3

Thread: SQL Update

  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: SQL Update

    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

  2. #2
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    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)
    A good programmer is a LAZY programmer!

  3. #3
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    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
    A good programmer is a LAZY programmer!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •