Jump to content

Delete Join Where Second Table Could Be Empty


Greywacke
 Share

Recommended Posts

hi there,i am trying to delete from two tables, one containing the leads and one containing the record of forwarding.however, this won't work if the second table returns an empty recordset.the left join seems to work, but it won't delete the record from the first table.here is the sql string:

DELETE FROM 10_serviceprospects LEFT JOIN 13_prospectleadsent ON 10_serviceprospects.bigint_ProspectID = 13_prospectleadsent.bigint_ProspectID WHERE 10_serviceprospects.bigint_ProspectID = 780;

a lead with id 780 does exist however.the query returns with the following error if i enable :

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 'LEFT JOIN
how could i modify this query to only attach the second table where that id exists? add a WHERE EXISTS subquery perhaps?how would i do this? i can't seem to find anything on the web...i guess i'm trying to do an optional delete join here. Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

well duh... i needed to use an OR not an AND originally...

DELETE FROM 10_serviceprospects, 13_prospectleadsent WHERE 10_serviceprospects.bigint_ProspectID = 780 OR 13_prospectleadsent.bigint_ProspectID = 780;

the 780 representing the ProspectID.

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...