Search...
FAQs
Subscribe
Pie
FAQs
Recent topics
Flagged topics
Hot topics
Best topics
Search...
Search within JDBC and Relational Databases
Search Coderanch
Advance search
Google search
Register / Login
Post Reply
Bookmark Topic
Watch Topic
New Topic
programming forums
Java
Mobile
Certification
Databases
Caching
Books
Engineering
Micro Controllers
OS
Languages
Paradigms
IDEs
Build Tools
Frameworks
Application Servers
Open Source
This Site
Careers
Other
Pie Elite
all forums
this forum made possible by our volunteer staff, including ...
Marshals:
Campbell Ritchie
Jeanne Boyarsky
Ron McLeod
Paul Clapham
Liutauras Vilda
Sheriffs:
paul wheaton
Rob Spoor
Devaka Cooray
Saloon Keepers:
Stephan van Hulst
Tim Holloway
Carey Brown
Frits Walraven
Tim Moores
Bartenders:
Mikalai Zaikin
Forum:
JDBC and Relational Databases
DELETE using subquery
Katrina Owen
Sheriff
Posts: 1367
18
posted 16 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
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:
SELECT t1.field FROM tableOne AS t1 LEFT OUTER JOIN tableTwo AS t2 ON t1.field = t2.field WHERE t2.field IS NULL;
However, I haven't been able to construct any DELETE statements that my database will process.
I've tried:
DELETE FROM tableOne WHERE field IN ( SELECT DISTINCT t1.field FROM tableOne AS t1 LEFT OUTER JOIN tableTwo AS t2 ON t1.field = t2.field WHERE t2.field IS NULL )
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:
DELETE FROM tableOne WHERE field NOT IN ( SELECT field FROM tableTwo );
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
posted 16 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
I ended up using temporary tables to store the rows I needed:
CREATE TABLE tmptable SELECT t1.* FROM table1 as t1 LEFT JOIN table2 as t2 ON t2.id = t1.id WHERE t2.id is NULL; DELETE FROM table1 USING tmptable, table1 WHERE table.id = tmptable.id; DROP TABLE tmptable;
David O'Meara
Rancher
Posts: 13459
I like...
posted 16 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Drat, I was about to suggest the same, but got pulled away for an hour...
David O'Meara
Rancher
Posts: 13459
I like...
posted 16 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
did you write that information back to the MySQL docs?
Katrina Owen
Sheriff
Posts: 1367
18
posted 16 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
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:
CREATE TEMPORARY TABLE tmptable SELECT A.* FROM table1 AS A, table1 AS B WHERE A.username LIKE '%2' AND A.ID = B.ID AND A.username <> B.username; DELETE table1 FROM table1 INNER JOIN tmptable ON table1.username = tmptable.username;
I guess next time I can use
TEMPORARY
, too
Rob Spoor
Sheriff
Posts: 22783
131
I like...
posted 16 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
AFAIK, MySQL doesn't support any subqueries at all until version 5.
SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6 - OCEJPAD 6
How To Ask Questions
How To Answer Questions
Consider Paul's
rocket mass heater
.
reply
reply
Bookmark Topic
Watch Topic
New Topic
Boost this thread!
Similar Threads
Compare Two Column
sql syntax question
From SQL to HQL
No Dialect mapping for JDBC type: -9
Inner and Outer Joins
More...