Results 1 to 3 of 3

Thread: UPDATE question

  1. #1
    Join Date
    Oct 2002
    Location
    Lund, Sweden
    Posts
    8

    Unanswered: UPDATE question

    Hi

    I have a table with an integer field. This field maybe NULL. If the field isn't null it should be separated by 10 numbers at all times. Can I do this with a signle UPDATE statement or do I have to write a more complex stored procedure to do this.

    Example the field could contain 10, 20, 30, 40, NULL, NULL, NULL.

    So if I put a new field lets say 12. Then I should recalc all fields to be 10, 20, 30, 40, 50, NULL, NULL, NULL.

    (The way to connect this to a trigger isn't a problem at this time. Only the UPDATE query).

    I'll use MS SQL server but it's nice if it is "as ANSI compartible" as possible.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: UPDATE question

    I don't have a database around to test this on today, but I think this may work:

    UPDATE table t1
    SET t1.n =
    ( SELECT (COUNT(*)+1)*10
    FROM table t2
    WHERE t2.n < t1.n
    )
    WHERE t1.n IS NOT NULL;

  3. #3
    Join Date
    Oct 2002
    Location
    Lund, Sweden
    Posts
    8
    Work great! Thanks.

Posting Permissions

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