Jump to content

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


Greywacke
 Share

Recommended Posts

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

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 by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...