Professional Web Applications Themes

Select-And-Update in one statement? - MySQL

On my site I often do SELECT counter from counters where id = 10 UPDATE counter set counter = counter + 1 Can I somehow merge them into one statement? Like (in C) SELECT counter++ from counters where id = 10 can that be done? i...

  1. #1

    Default Select-And-Update in one statement?

    On my site I often do

    SELECT counter from counters where id = 10
    UPDATE counter set counter = counter + 1

    Can I somehow merge them into one statement?

    Like (in C)

    SELECT counter++ from counters where id = 10

    can that be done?

    i

    Ignoramus23298 Guest

  2. #2

    Default Re: Select-And-Update in one statement?

    Ignoramus23298 <ignoramus23298NOSPAM.23298.invalid> wrote:
    >
    > SELECT counter from counters where id = 10
    > UPDATE counter set counter = counter + 1
    >
    > Can I somehow merge them into one statement?
    You can do READ-MODIFY-WRITE in a single statement (and thus:
    free from race condition) like this:

    UPDATE counters SET counter = (x:= counter) + 1 WHERE id = 10

    However you still need a second statement to read the (old)
    value of the counter:

    SELECT x

    In case you wonder what x is - consult the manual
    <http://dev.mysql.com/doc/refman/5.0/en/user-variables.html>


    HTH, XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. Select Distinct Statement Help
    By Ralph Freshour in forum PHP Development
    Replies: 5
    Last Post: August 20th, 07:27 AM
  3. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  4. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  5. Update and Select Statement
    By hngo01 in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 4th, 01:32 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139