[drupal-devel] Mysql problem

[Gordon Heydon]

Hello,

Gerhard Killesreiter wrote:

>On Thu, 12 May 2005, Gordon Heydon wrote:
>
>  
>
>>db_query('UPDATE {ec_ad_team} at LEFT JOIN {ec_transaction} t ON
>>at.txnid = t.txnid SET at.did = %d WHERE t.workflow = %d and at.did =
>>0', $did, 6);
>>
>>The problem is that for some reason the on my clients mysql server
>>doesn't seem to do the update, can anyone give me a reason why this
>>would be happening, and a possible method of resolving this.
>>    
>>
>
>UPDATE does not support JOINs according to the mysql manual.
>
>  
>
Beyond 4.0.4 it does, and on my 4.0.23 version of mysql it works fine. I
am just not sure how I can code around this. I was thinking that I could
do some think the following

$result = db_query('select at.txnid from {ec_ad_team} at LEFT JOIN
{ec_transaction} t ON at.txnid = t.txnid WHERE t.workflow = %d and
at.did = 0',...);
while ($row = db_fetch_aray($result)) {
    db_query('update {ec_ad_team} at set at.did = %d where at.txnid =
%d', ...);
}

The only problem with this is that it will not scale very well, and if
you have 100, 1000, 10000 row it will take forever to complete.

any ideas, thoughts would be appriecitated.

Thank in advance.
Gordon.