Issue with UPDATE on selected records in array

[bulgin]

Using Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2

 

The following bit of code selects a bunch of records:

 

$mailer = mysql_query("SELECT substring( web3_access_log.request_uri, 2 ) , web3_access_log.sent_or_not_sent, web3_access_log.logs_message_subject, authors.au_email_subject,  authors.au_widget 
FROM web3_access_log JOIN authors ON substring( web3_access_log.request_uri, 2 ) = authors.au_widget where web3_access_log.sent_or_not_sent = '0'") or die (mysql_error());

 

and then I create an email and send it out with some code like:

 

while($user = mysql_fetch_assoc($mailer)){
$original_subject=$user['au_email_subject'];
$id_reference=$user['id'];
$time=$user['real_time'];
$original_time=$user['time_date']; 
$ip=$user['remote_host'];.........

........

 

Now this is where I'm stuck: after the email is sent out I need to ONLY update the records which the above select statement chose and put into the array so that sent_or_not_sent is now set to 1, but I don't want to update that field if they were not chosen by the above select statement.

 

Currently I have:

 

mysql_query("UPDATE web3_access_log set sent_or_not_sent = 1 where web3_access_log.sent_or_not_sent = '0'") or die (mysql_error());

 

but this is updating ALL of the records in the web3_access_log that have sent_or_not_sent set at '0'.  I only want to update those chosen with the above criteria.  Is there some way to use the array to do this or am I way off base in that approach?

 

How would I tweak this?  Any help much appreciated.

 

[fenway]

Why not write a multi-table update?

[bulgin]

This is what I ended up with and it seems to work.  Do you see any issues with it?

 

mysql_query("UPDATE web3_access_log, authors set web3_access_log.sent_or_not_sent = 1 where substring( web3_access_log.request_uri, 2 ) = authors.widget") or die (mysql_error());