MySql - Select and Update in the same query?

[JerseyFoo]

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.

 

[DDRtists]

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. "

 

[JerseyFoo]

Would you happen to know if the select will occur immediately after the update?

 

[DDRtists]

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.

 

[Samuraid]

Would you happen to know if the select will occur immediately after the update?

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.

 

[JerseyFoo]

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.

 

[Samuraid]

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.