Greywacke Posted April 9, 2010 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 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 Share Posted April 9, 2010 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. Link to comment Share on other sites More sharing options...
Greywacke Posted April 10, 2010 Author 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 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 Share Posted April 10, 2010 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); Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.