How can I increment value in column of single row table?

Discussion in 'MySQL' started by Sleeping Troll, Aug 16, 2008.

0
  1. #1
    This doesn't work:

    Update Tracking Set Return = Return + 1
     
    Sleeping Troll, Aug 16, 2008 IP
  2. net-split

    net-split Peon

    Messages:
    29
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    that would change all values in that column within the table, are you sure you do not want a where clause to prevent that from happening?

    The statement should work, atleast in MSSQL.
     
    net-split, Aug 18, 2008 IP
  3. cont911

    cont911 Peon

    Messages:
    50
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #3
    "Return" is a reserved word in mysql. See
    dev.mysql.com/doc/refman/5.0/en/reserved-words.html

    use syntax below or rename field name
    update Tracking set `Return`=`Return`+1
     
    cont911, Aug 18, 2008 IP
  4. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #4
    You could try to end your statement with a Update Tracking Set Return = Return + 1 LIMIT 1
    If I remember correctly this should limit the Mysql update statement to 1 record.
    But keep in mind that you can't keep track of the updated record since this would only change the first record that the storage engine returns, without corresponding where and ORDER BY you have no control since the storage engine does not guarantee the ordering of the data unless specified via the statement to do so.
     
    chisara, Sep 15, 2008 IP