CODEWITHSUNDEEP
phpmysqlsql
Jake
Jake

Reputation: 3486

Combining 3 MySQL queries into one

What I'm trying to do is take a whole table and find out what the id is of the member whose email address is in the value of emails.from and put their id in there instead. I'm trying to improve my performance by using IDs instead of email addresses but am unable to complete this task.

$mysql = new mysql();
$mysql->query('SELECT `from` FROM `emails` ORDER BY `id` ASC' );

while($row = mysql_fetch_array($mysql->result)){
    $mysql2 = new mysql();
    $mysql2->query('SELECT `id` FROM `exchange` WHERE `email` = "'.$row['from'].'"');

    $details = mysql_fetch_assoc($mysql2->result);


    $mysql2->query('UPDATE `emails` SET `from` = '.$details['id'].' WHERE `from` = "'.$row['from'].'"');
}

Upvotes: 3

Views: 115

Answers (3)

Stijn Leenknegt
Stijn Leenknegt

Reputation: 1347

The query below should do the trick :)

update emails 
set `from`=(select e.id from exchange as e where e.email=em.`from`)
from emails as em
order by em.id asc

Upvotes: 1

biziclop
biziclop

Reputation: 14596

If I understand your code, you could try something like this:

UPDATE     `emails`
INNER JOIN `exchange` ON `exchange`.`email` = `emails`.`from`
SET `emails`.`from` = `exchange`.`id`

Upvotes: 1

Cfreak
Cfreak

Reputation: 19309

No need to look it up if you're doing the whole table.

UPDATE emails SET from=id

Will do each row in the table.

Upvotes: 2

Related Questions