[drupal-devel] Mysql problem
Hello, I have created an event logging system for use with the ecommerce module, and when I download the event data I execute the following query so that the row that have not been downloaded will get marked with a unique batch id so I can download them without risk of loss of data. 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. Thanks in advance. Gordon.
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. Cheers, Gerhard
From the MySQL manual: Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables. The table_references part lists the tables involved in the join. Its syntax is described in Section 13.1.7.1, “JOIN Syntax”. Here is an example: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN. What version of MySQL are you running on, Gordon? On 11 May 2005, at 10:39 PM, 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.
Cheers, Gerhard
Djun
Hello, Thanks for this, I have made the change that I suggested before, but I would still like a better solution that I cam implement. Thanks Gordon On Wed, 2005-05-11 at 23:58 -0700, puregin wrote:
From the MySQL manual:
Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables. The table_references part lists the tables involved in the join. Its syntax is described in Section 13.1.7.1, “JOIN Syntax”. Here is an example:
UPDATE items,month SET items.price=month.price WHERE items.id=month.id; The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.
What version of MySQL are you running on, Gordon?
On 11 May 2005, at 10:39 PM, 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.
Cheers, Gerhard
Djun
!DSPAM:42830048240491408311589!
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.
participants (3)
-
Gerhard Killesreiter -
Gordon Heydon -
puregin