File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes DELETE using subquery Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DELETE using subquery" Watch "DELETE using subquery" New topic
Author

DELETE using subquery

Katrina Owen
Sheriff

Joined: Nov 03, 2006
Posts: 1367
    
  18
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

Joined: Nov 03, 2006
Posts: 1367
    
  18
I ended up using temporary tables to store the rows I needed:

David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Drat, I was about to suggest the same, but got pulled away for an hour...
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

did you write that information back to the MySQL docs?
Katrina Owen
Sheriff

Joined: Nov 03, 2006
Posts: 1367
    
  18
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

Joined: Oct 27, 2005
Posts: 19792
    
  20

AFAIK, MySQL doesn't support any subqueries at all until version 5.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DELETE using subquery