mysql statment help

[sasha85]

i got this line before:

mySQLde="UPDATE crmagents SET online=NULL WHERE online < DATE_ADD(LOCALTIME(),INTERVAL -20 MINUTE)"


it working perfectly! it was nulling all the records older than 20min

but i needed to add the nulling opration of the fields on the second table than i wrote this
mySQLde="UPDATE crmagents, crminfo SET crmagents.online = NULL , crminfo.Agent = NULL WHERE crmagents.online < DATE_ADD(LOCALTIME(),INTERVAL -20 MINUTE) AND crminfo.agent = crmagents.username"

and now even the old records do not nulling...

help!!

[Fumigator]

I haven't done much with JOINs and UPDATEs, but I can't help but think it's going to be a problem if you update the column upon which your join is based.

Maybe you should separate the two operations-- select the rows you want to update, then perform updates on each result. Sounds inefficient but just to get things working you may want to try it.

[sasha85]

it looks for those records that are older than 20 min, it takes the name of those old records and nulling records filed with the same name in another table.

[guelphdad]

right but what Fumigator is telling you is that you are joining on this condition:
crminfo.agent = crmagents.username
but at the same time trying to set crminfo.Agent to NULL.

perhaps you can show some sample data in both tables and then tell us what you want to end up with.