join

[sasha85]

i got 2 tables in the first table "crmagents" all the usernames stored in column 'username' and 'online'
the seconds table "crminfo" got 2 columns 'agent'
*column 'agent' got names from the first table crmagents.

i need mysql\sql query that will update crminfo.agent=null where crmagents.online=null

mySQLde="UPDATE crminfo JOIN crmagents ON crminfo.agent = crmagents.username SET crminfo.Agent = NULL WHERE crmagents.online = null"


it is not working:(

[nikkiH]

You can't use a join for an update like that. Use a sub-select.

update crminfo set agent = NULL
WHERE agent IN
(select username from crmagents where online is null)

[SouthwaterDave]

I don't know about MySQL but you can use a join in SQL Server:


update crminfo
set crminfo.Agent = null
from crminfo a
inner join crmagents on a.agent = crmagents.username and crmagents.online = null

[nikkiH]

Ah, see, I had made the same mistake and tried the join in the wrong spot. Ended up with a sub-select.

Thanks for sharing that way :)