On 30-Apr-2003 Stephen Fromm wrote:
> I'm using MySQL 3.23.56. (In particular, subselects are not available,
> nor
<snip>
> 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
variable:
SELECT @orphan:=id FROM foo LEFT JOIN bar ... LIMIT 1;
DELETE FROM foo WHERE id=@orphan;
Regards,
--
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)
