Greywacke Posted April 9, 2010 Report Share Posted April 9, 2010 hi, me again...battling with the following statement: DELETE FROM 6_serviceleads, 10_serviceprospects JOIN 13_serviceprospectsent ON (10_serviceprospects.bigint_ProspectID = 13_serviceprospectsent.bigint_ProspectID) WHERE 6_serviceleads.text_Consumer LIKE "%pierre@greywacke.co.za%" OR 10_serviceprospects.text_Consumer LIKE "%pierre@greywacke.co.za%"; the service leads is an independent table in this situation, the service prospects has multiple entries per prospect. i believe i can use a WHERE IN clause or something but my clarity of intent has faded.i'll let you guys play around with it in the mean time Link to comment Share on other sites More sharing options...
justsomeguy Posted April 9, 2010 Report Share Posted April 9, 2010 I'm not quite sure what you're trying to do with a join in a delete query, which table are you trying to delete from? Link to comment Share on other sites More sharing options...
Greywacke Posted April 9, 2010 Author Report Share Posted April 9, 2010 (edited) i need to delete from all 3 of them: 1) all records in leads table (6_serviceleads) with the text_Consumer field containing pierre@greywacke.co.za 2) all records in prospect leads table (10_serviceprospects) with the text_Consumer field containing pierre@greywacke.co.za 3) all records in prospect leads sent table (13_serviceprospectsent) bound by bigint_ProspectID in table 2.DELETE JOIN statements are allowed according to the Official MySQL Documentation. Edited April 9, 2010 by Pierre 'Greywacke' du Toit Link to comment Share on other sites More sharing options...
Greywacke Posted April 10, 2010 Author Report Share Posted April 10, 2010 okay after revisiting this issue, i have come up with the following solution: DELETE FROM 6_serviceleads WHERE text_Consumer LIKE "%pierre@greywacke.co.za%";DELETE FROM 10_serviceprospects WHERE text_Consumer LIKE "%pierre@greywacke.co.za%";DELETE FROM 13_prospectleadsent WHERE bigint_ProspectID NOT EXISTS (SELECT * FROM 10_serviceprospects WHERE text_Consumer LIKE "%pierre@greywacke.co.za%"); to rather do it as three seperate queries. however, i receive an error here saying the following: MySQL said: Documentation#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT * FROM 10_serviceprospects WHERE text_Consumer LIKE "%pierre@grey' at line 1this is on the third and last query now. what would be wrong here? Link to comment Share on other sites More sharing options...
Greywacke Posted April 10, 2010 Author Report Share Posted April 10, 2010 ahh the subselect must attempt to select by lead id - doh Link to comment Share on other sites More sharing options...
Greywacke Posted April 10, 2010 Author Report Share Posted April 10, 2010 (edited) awesome ^^seems this issue has been cleared :)was supposed to delete in the leads and prospectleads tables first, then delete the sent records that don't have matches in the prospectleads table. :)ISSUE RESOLVED!!! (didn't realise that PMA parses multiple sql statements ) DELETE FROM 6_serviceleads WHERE text_Consumer LIKE "%pierre@greywacke.co.za%";DELETE FROM 10_serviceprospects WHERE text_Consumer LIKE "%pierre@greywacke.co.za%";DELETE FROM 13_prospectleadsent WHERE NOT EXISTS (SELECT * FROM 10_serviceprospects WHERE 10_serviceprospects.bigint_ProspectID = 13_prospectleadsent.bigint_ProspectID); Edited April 10, 2010 by Pierre 'Greywacke' du Toit Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now