FAQ
I need to do something similar to replace, but can't figure out how to do it.

Here's my table:
user varchar(15) PRI
cnt int(11)

I want to increment cnt for user if it exists, else insert a row with cnt=1. I tried the following, but mysql complains that I can't use the same table in the update and select:

replace into table set user='someuser', cnt=(select cnt+1 from table where user='someuser')

Can this be done (in 1 query, of course)?

Thanks,
Juan E

Search Discussions

  • Victoria Reznichenko at Feb 19, 2004 at 7:45 am

    "Juan E Suris" wrote:
    I need to do something similar to replace, but can't figure out how to do it.

    Here's my table:
    user varchar(15) PRI
    cnt int(11)

    I want to increment cnt for user if it exists, else insert a row with cnt=1. I tried the following, but mysql complains that I can't use the same table in the update and select:

    replace into table set user='someuser', cnt=(select cnt+1 from table where user='someuser')

    Can this be done (in 1 query, of course)?
    From v4.1.0 MySQL supports INSERT .. ON DUPLICATE KEY UPDATE and you can write your query as

    INSERT INTO table VALUES('someuser', 1)
    ON DUPLICATE KEY UPDATE cnt=cnt+1;

    http://www.mysql.com/doc/en/INSERT.html


    --
    For technical support contracts, goto https://order.mysql.com/?ref=ensita
    This email is sponsored by Ensita.net http://www.ensita.net/
    __ ___ ___ ____ __
    / |/ /_ __/ __/ __ \/ / Victoria Reznichenko
    / /|_/ / // /\ \/ /_/ / /__ victoria.reznichenko@ensita.net
    /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
    <___/ www.mysql.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupmysql @
categoriesmysql
postedFeb 19, '04 at 5:18a
activeFeb 19, '04 at 7:45a
posts2
users2
websitemysql.com
irc#mysql

People

Translate

site design / logo © 2021 Grokbase