MySql - Select and Update in the same query?

Discussion in 'General Webmaster Support' started by JerseyFoo, Jun 29, 2009.

  1. JerseyFoo

    JerseyFoo 1/g = g-1

    Ratings:
    +40 / 0 / -0
    I'd like to set a value + 1, and then select it using MySql. Anyone happen to know how, I've only found implementations that are a lot heavier than what I need.
     
  2. DDRtists

    DDRtists ɹoʇɐɹǝpoɯ ɹǝdns Staff Member

    Ratings:
    +413 / 0 / -0
    I'm pretty sure you'd have to use two queries.

    UPDATE table SET column = column + 1 WHERE idcolumn = 1;
    SELECT column FROM table WHERE idcolumn = 1;

    http://dev.mysql.com/doc/refman/5.0/en/update.html
    "Currently, you cannot update a table and select from the same table in a subquery. "
     
  3. JerseyFoo

    JerseyFoo 1/g = g-1

    Ratings:
    +40 / 0 / -0
    Would you happen to know if the select will occur immediately after the update?
     
  4. DDRtists

    DDRtists ɹoʇɐɹǝpoɯ ɹǝdns Staff Member

    Ratings:
    +413 / 0 / -0
    Uh, if you put the select query right after the update query, then yes. They will be two different calls to mysql though, as like that page says, you can't update and select in the same query.

    If you're asking if it will be updated yet when selecting it, yes it will be updated.
     
  5. Samuraid

    Samuraid Advisor

    Ratings:
    +80 / 0 / -0
    You are not guaranteed to have one occur right after the other. Another connection may query the data in between your two queries.

    The only way around this is to use a database engine with proper transaction support (like MySQL InnoDB, or PostgreSQL) and set the transaction isolation level to READ COMMITTED or higher. Then enclose your two queries within a single transaction.
     
  6. JerseyFoo

    JerseyFoo 1/g = g-1

    Ratings:
    +40 / 0 / -0
    Shucks.

    Would you happen to know any better ways to go about a multi-table?

    c1 i1
    c1 i2
    c2 i1
    c1 i3

    ^ dunno if that illustrates what I mean correctly, a system of auto-increment for multiple channels of rows.
     
  7. Samuraid

    Samuraid Advisor

    Ratings:
    +80 / 0 / -0
    You could make the first table auto_increment, and the second table normal (no increment).

    Insert into the first table, use mysql_insert_id() to get the ID number that was inserted, then manually insert into the second table using the ID number you got back.

    That would work for a single channel of rows. As for multiple...you need a DBMS that allows you to define a custom sequence, or has triggers of some sort.
     

Share This Page