This week's book giveaway is in the Features new in Java 9 forum.
We're giving away four copies of Java 9 Revealed and have Kishori Sharan on-line!
See this thread for details.
Win a copy of Java 9 Revealed this week in the Features new in Java 9 forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

DELETE using subquery  RSS feed

 
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
Posts: 20941
81
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
Boost this thread!