>> 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
>--=_alternative 006B5FB085257052_=--
Hi again,
mysql> show create table banners\G;
*************************** 1. row ***************************
Table: banners
Create Table: CREATE TABLE `banners` (
`id` int(10) NOT NULL auto_increment,
`did` int(10) NOT NULL default '0',
`text` varchar(255) NOT NULL default 'not specified',
`count` int(10) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql> show create table domains\G;
*************************** 1. row ***************************
Table: domains
Create Table: CREATE TABLE `domains` (
`id` int(10) NOT NULL auto_increment,
`domain` varchar(255) NOT NULL default '',
`count` int(10) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM
1 row in set (0.00 sec)
Thanks, With best regards,
Gorohov Dmitry
----------------------------------------------
Contact information:
MSN: [EMAIL PROTECTED]
AOL: chrislee943
ICQ: 284144732
phone: +(10) 375 29 5680605
----------------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
