List:General Discussion« Previous MessageNext Message »
From:Don Read Date:May 1 2003 4:21am
Subject:RE: DELETE without subselect
View as plain text  
On 30-Apr-2003 Stephen Fromm wrote:
> I'm using MySQL 3.23.56.  (In particular, subselects are not available,
> nor

> How can I DELETE rows from a table based on the results from a join?  I
> want
> to remove rows that don't obey a foreign key constraint.  I can find the
> rows that don't obey the foreign key constraint using LEFT JOINs.  But
> then
> I don't see how I can get those rows into a WHERE clause in a DELETE
> statement, since I cannot use subselects.  (Of course, I could write the
> whole thing in the C API, but I want to know if it's possible to do this
> using SQL statements from within the MySQL client, alone.)

If you're willing to burn some cycles, you could loop on a user 

SELECT @orphan:=id FROM foo LEFT JOIN bar ... LIMIT 1;
DELETE FROM foo WHERE id=@orphan;

Don Read                                       dread@stripped
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
                            (53kr33t w0rdz: sql table query)

DELETE without subselectStephen Fromm30 Apr
  • RE: DELETE without subselectDon Read1 May