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 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2011
    Posts
    42
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Help with subquery..

    I've never used subqueries before and after trying for a while I still haven't..

    I had two statements before. I would save the result from the first to a variable ($result) and use it in the second.

    SELECT field1 FROM table WHERE (field1 ='$x' AND field2 = '$y');

    UPDATE table SET field3 = '$result', field1 = NULL, field2 = NULL WHERE field1 ='$x' AND field2 = '$y' LIMIT 1";

    Am I right in thinking I can do this in one query using a subquery? I came up with the below but its not working so obviously I'm doing something wrong.

    UPDATE table SET field3 = (SELECT field1 FROM table WHERE field1 ='$x' AND field2 = '$y'), field1 = NULL, field2 = NULL WHERE field1 ='$x' AND field2 = '$y' LIMIT 1";

    Any help would be great!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    You do *NOT* want a subquery for this.

    MySQL UPDATE syntax is weird but a little more user-friendly than the syntax in other DBs.

    http://dev.mysql.com/doc/refman/5.5/en/update.html

    Code:
    UPDATE table 
    SET field3 = field1, field1 = NULL, field2 = NULL 
    WHERE field1 ='$x' AND field2 = '$y' LIMIT 1
    But that will update field3 with field1 *FROM THE SAME RECORD*.

    If your
    Code:
    SELECT field1 FROM table WHERE (field1 ='$x' AND field2 = '$y');
    can return more than one record, then I don't understand what you are trying to do.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    CrazyCrisUk (11-01-2011)

  • #3
    New Coder
    Join Date
    Jan 2011
    Posts
    42
    Thanks
    5
    Thanked 0 Times in 0 Posts
    It's to activate an email account.

    Field 1 is the temporary holding cell for the email address.

    Field 2 is the activation code sent in an email to user.

    Field 3 is the active email address.

    So it's checking that the email and activation code match those in the database and then moving the email address from the holding cell to the active cell, and setting the holding cell and activation fields to NULL.

    Thats the idea anyway...

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Happiness. Use what I showed you.

    Assuming that all your activation codes are unique, that's the answer. (And you can ensure they are unique by creating a UNIQUE KEY on that field.)

    Oh...and there's no reason for the LIMIT 1, then. It won't hurt, but if the activation codes are unique then there's never a possibility of more than one record being updated. (Actually, it's the combination of email address and code that has to be unique.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Jan 2011
    Posts
    42
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks, I didn't know you could copy fields like that, will come in handy

    I'll check out the unique key too, I currently use
    PHP Code:
    md5(uniqid(rand(), true)) 
    to generate the activation code but it isn't fool proof. The unique key could be useful to regenerate the code if it isn't unique.

    Thanks for the advice.


  •  

    Posting Permissions

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