cleaning up testing quote requests from 2 tables and the sent records in a 3rd

[Greywacke]

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

[justsomeguy]

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?

[Greywacke]

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

[Greywacke]

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 1

this is on the third and last query now. what would be wrong here?

[Greywacke]

ahh the subselect must attempt to select by lead id - doh

[Greywacke]

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