Page 1 of 1

mysql subquery help... again

Posted: Thu Jul 10, 2008 3:20 am
by s.dot
I think this is possible to do at the database level in one query instead of using PHP to loop and create thousands of queries. I seem to be having lots of problems with this lately.

OK, here's the scenario. I have two tables. The first is a standard members table with `id`, `last_logged_in`, and `member_type` fields among others.

The second table contains the same 3 fields I mentioned specifically above. However, this table is only updated once per week on a crontab script.

So, I would like to update the second table using ONE query with the respective data from the first tables.

Here's my pathetic attempts :P

Code: Select all

UPDATE `table_two` SET `last_logged_in` = (SELECT `last_logged_in` FROM `table_one`)
Error: More than one row returned in subquery

Code: Select all

UPDATE `table_two` SET `last_logged_in` = (SELECT `last_logged_in` FROM `table_one`) GROUP BY `member_id`
Error: Syntax error near 'GROUP BY'

Re: mysql subquery help... again

Posted: Thu Jul 10, 2008 3:28 am
by s.dot
Here's what I'm trying to avoid doing in PHP:

Code: Select all

$membersWhoVoted = array();
$membersIdResult = mysql_query("SELECT DISTINCT `member_id` FROM `votestable`")
    or die(mysql_error());
 
while ($membersIdArray = mysql_fetch_assoc($membersIdResult))
{
    $membersWhoVoted[] = $membersIdArray['member_id'];
}
 
//chunk it into a list of 300 to avoid overly large queries being sent to 
//the server
$chunkedMembers = array_chunk($membersWhoVoted, 300);
 
foreach ($chunkedMembers AS $membersWhoVoted)
{
    $membersWhoVoted = implode(', ', $membersWhoVoted);
    $memberDetails = array();
    
    //get the details for these members who voted
    $memberDetailsResult = mysql_query("SELECT `id`, UNIX_TIMESTAMP(`lastlogin`) AS `last_login`, `member_type` FROM `members` WHERE `id` IN($membersWhoVoted)") or die(mysql_error());
    while ($memberDetailsArray = mysql_fetch_assoc($memberDetailsResult))
    {
        $memberDetails[$memberDetailsArray['id']] = array('last_login' => $memberDetailsArray['last_login'], 'member_type' => $memberDetailsArray['member_type']);
    }
    
    foreach ($memberDetails AS $member_id => $details)
    {
        mysql_query("UPDATE `tabletwo` SET `last_login` = '" . $details['last_login'] . "', `member_type` = '" . $details['member_type'] . "' WHERE `member_id` = '$member_id'") or die(mysql_error());
    }
}
 

Re: mysql subquery help... again

Posted: Thu Jul 10, 2008 3:37 am
by EverLearning
According to UPDATE syntax in MySql manual this should work:

Code: Select all

UPDATE `table_one`, `table_two`
    SET `table_two`.`last_logged_in` = `table_one`.`last_logged_in`
    WHERE `table_two`.`id` = `table_one`.`id`

Re: mysql subquery help... again

Posted: Thu Jul 10, 2008 3:45 am
by s.dot
YOU'RE A GENIUS. :mrgreen:

Thank you, it worked beautifully.