"Christian Lee" <[EMAIL PROTECTED]> wrote on 08/03/2005 02:19:30 PM:
> Hi all, > > I've a question to guru :) > there're two tables: > mysql> show fields from domains; > +--------+--------------+------+-----+---------+---------------- > | Field | Type | Null | Key | Default | Extra > +--------+--------------+------+-----+---------+---------------- > | id | int(10) | | PRI | NULL | auto_increment > | domain | varchar(255) | | | | > | count | int(10) | | | 0 | > +--------+--------------+------+-----+---------+---------------- > 3 rows in set (0.01 sec) > > mysql> show fields from banners; > +-------+--------------+------+-----+---------------+----------- > | Field | Type | Null | Key | Default | Extra > +-------+--------------+------+-----+---------------+----------- > | id | int(10) | | PRI | NULL | auto_incre > | did | int(10) | | | 0 | > | text | varchar(255) | | | not specified | > | count | int(10) | | | 0 | > +-------+--------------+------+-----+---------------+----------- > 4 rows in set (0.00 sec) > and a query: > > select ifnull(domains.id,0) as id, > ifnull(domains.domain,'no such domain') as domain, > if(b.count,b.count+1,0) as count > from banners b > inner join domains on b.did=domains.id > where domains.domain like 'domain.com'; > > I think this query is pretty simple, and I want to do following: > select ifnull(domains.id,0) as id, > ifnull(domains.domain,'no such domain') as domain, > > /// here I want to get the b.count already incremented (+1) > /// and update my column b.count with new value in one query. > /// for example: > /// if(b.count,(update b set count:=count + 1),0) as count > > from banners b > inner join domains on b.did=domains.id > where domains.domain like 'domain.com'; > > How to do it, thanks for any help! > > > Thanks, With best regards, > > Gorohov Dmitry > ---------------------------------------------- > Contact information: > MSN: [EMAIL PROTECTED] > AOL: chrislee943 > ICQ: 284144732 > phone: +(10) 375 29 5680605 > ---------------------------------------------- > Nope, it's not going to happen in a single statement. The MySQL UPDATE command does not return a recordset and the SELECT command cannot change a value on a table. This is the exact situation that explicit table locking and transactions were created to solve. However, you did not say which engine your tables are using (MyIsam or InnoDB). I cannot tell you explicitly how to solve your problem without knowing which option I have available to me. Instead of the results from "show fields from xxx;" can you post the results of "show create table xxx\G" for both of your tables? Then the list will have the information we need to give you a working solution. (I guess you could also just tell us which engine you are using but the other way give us more information) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
