Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: incrementing value with 1 query

    Is there a way to increment a value without first doing a SELECT query, then adding 1 to it, then doing an UPDATE query?

    I'm running SQL 7 with ASP.

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You could use subquery - something like this:

    update table set id=(select max(id) from table2)
    where .....

  3. #3
    Join Date
    Dec 2002
    Posts
    29
    What about

    Update TableName
    Set ColumnName = ColumnName + 1
    Where Condition

    ???
    Why isn't this working for you?

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Re: incrementing value with 1 query


    Is there a way to increment a value without first doing a SELECT query, then adding 1 to it, then doing an UPDATE query?
    One way is:

    UPDATE table SET field = field + 1

    One problem that always surfaces is that of timing ... you need to predictably know what the new value is and that no one else will inadvertantly think he's gotten the same value. One way to do that is with a transaction. Another way to do it is to SELECT the value (say it's 7) and then:
    UDPATE table SET field = 8 WHERE field = 7;

    If that succeeds then you know that the value "7" is yours. If it fails then you know you're racing with someone else. Wait a random period of time and try again.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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