List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 8 1999 8:22pm
Subject:Re: Is it possible to do joins in delete statements?
View as plain text  
David Johnson wrote:
> 
> I want to do the following to delete all orphans in one of my tables called STATS :
> 
> DELETE from STATS left join DBUSER on STATS.fk_dbuser = DBUSER.id where DBUSER.id is
> null;
> 
> and I get a SQL error:
> 
> ERROR 1064: You have an error in your SQL syntax near 'left join DBUSER on
> STATS.fk_aguser = DBUSER.id where DBUSER.id is null' at line 1
> 
> Does this mean I can't do a join in a delete statement?  If so, what's the best way
> to delete all these records?
> 
> DJ

Yes, joins do not work in a delete statement. You probably would have to
come up with some trick here. Here is one way do it:

alter table stats add orphan enum ('Y','N') default 'Y'
replace into stats select <all fields from stats except orphan>, 'N'
from stats,      dbuser where stats.fk_aguser = dbuser.id;

delete from stats where orphan = 'Y';
alter table stats drop orphan

This should work, but I have not test it. Make sure you understand what
I am doing before you use it, and run the select inside the replace on
its own to make sure it actually selects what you need and only then run
it with replace. Also make sure you have a recent backup of your tables,
since a typo in a delete or replace could ruin your data.

-- 
Sasha Pachev
http://www.sashanet.com
Thread
Is it possible to do joins in delete statements?David Johnson8 May
  • Re: Is it possible to do joins in delete statements?Sasha Pachev8 May