View Full Version : join

11-13-2007, 03:55 PM
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:(

11-13-2007, 09:01 PM
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)

11-13-2007, 09:53 PM
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

11-13-2007, 10:16 PM
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 :)