need help with SQL error

[webguync]

Hi, the statement gives me an error

DELETE FROM log_April2010 WHERE user_id = 1 AND roster_April2010.user_id = 1

 

trying to delete a record in two different tables where the field user_id is the same.

 

the error is #1054 - Unknown column 'roster_April2010.user_id' in 'where clause'

 

however there is a column called user_id in roster_April2010, so what am I doing wrong?

[JustLikeIcarus]

How about this?

 

DELETE FROM log_April2010, roster_April2010 
USING log_April2010 INNER JOIN roster_April2010
WHERE log_April2010.user_id = roster_April2010.user_id 
AND log_April2010.user_id = 1;

 

[webguync]

not quite b/c that deletes two rows (one in the log file and one in the roster file). I only want to delete the record in the log file, but it has to match up with the user_id in the roster file.

 

really trying to do something in PHP like

$query_delete = "DELETE FROM log_April2010 WHERE user_id = $user_id AND roster_April2010.user_id = $user_id";

 

but the SQL produces an error and trying it in PHPMyAdmin produces an error also.

[JustLikeIcarus]

OK try changing mine to this.

DELETE FROM log_April2010
USING log_April2010 INNER JOIN roster_April2010
WHERE log_April2010.user_id = roster_April2010.user_id
AND roster_April2010.user_id = 1;

[webguync]

cool, that seems to do what I want it to. Now I just need to figure out the PHP part :-)

 

thanks!