• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DELETE using subquery

 
Katrina Owen
Sheriff
Posts: 1367
18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I inherited the maintenance of a monolithic web application with a MySQL 4.1 backend.

I am trying to clean out orphaned records (which are counted in the thousands, unfortunately).

I've been able to determine exactly which records should go using the following type of statement:



However, I haven't been able to construct any DELETE statements that my database will process.

I've tried:



Since google says that MySQL 4.1 doesn't support deleting with a subquery that selects from the same table as you are deleting from, I also tried:



In both cases, the error message says I have a syntax error at the location where my subquery starts.

Any suggestions as to what I can try next?

Katrina
[ January 13, 2008: Message edited by: Katrina Owen ]
 
Katrina Owen
Sheriff
Posts: 1367
18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I ended up using temporary tables to store the rows I needed:

 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Drat, I was about to suggest the same, but got pulled away for an hour...
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
did you write that information back to the MySQL docs?
 
Katrina Owen
Sheriff
Posts: 1367
18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, good point. Not sure where the best place would be... under DELETE, simply?

http://dev.mysql.com/doc/refman/4.1/en/

Huh. Someone there ran into something similar, and posted the following:



I guess next time I can use , too
 
Rob Spoor
Sheriff
Pie
Posts: 20608
63
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
AFAIK, MySQL doesn't support any subqueries at all until version 5.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic