mysql subquery help... again

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

mysql subquery help... again

Post 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'
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: mysql subquery help... again

Post 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());
    }
}
 
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: mysql subquery help... again

Post 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`
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: mysql subquery help... again

Post by s.dot »

YOU'RE A GENIUS. :mrgreen:

Thank you, it worked beautifully.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply