Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: join

  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    179
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Exclamation join

    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

    Code:
    mySQLde="UPDATE crminfo JOIN crmagents ON crminfo.agent = crmagents.username SET crminfo.Agent = NULL WHERE crmagents.online = null"
    it is not working
    By the time u recognize this moment, This moment will be gone.

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    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)

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Regular Coder
    Join Date
    May 2007
    Location
    UK
    Posts
    180
    Thanks
    0
    Thanked 18 Times in 18 Posts
    I don't know about MySQL but you can use a join in SQL Server:

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

  • #4
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    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

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •