View Full Version : Help with subquery..

10-31-2011, 11:47 PM
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! :thumbsup:

Old Pedant
11-01-2011, 01:02 AM
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.


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

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.

11-01-2011, 01:07 AM
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...

Old Pedant
11-01-2011, 01:14 AM
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.)

11-01-2011, 01:32 AM
Thanks, I didn't know you could copy fields like that, will come in handy :thumbsup:

I'll check out the unique key too, I currently use
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.