Hello,

I have racked my brain and resources on this one. It must be so obvious that I can't see it 🙂 I have a pretty simple query that deletes records that don't exist in other tables. I am using a UNION after a NOT IN subquery. I am geting a syntax error returned. Can anyone see what is wrong with my syntax:

DELETE FROM
	properties_xref_features
WHERE
	xref_properties_id NOT IN (
		SELECT
			xref_properties_id
		FROM
			properties_new
		UNION
		SELECT
			id
		FROM
			properties
		WHERE
			featured = 1
	)

THANK YOU!!!

-Sean

    What version of MySQL are you using? Subselects aren't supported until 4.1

      I am using 4.0.18. I thought that this was supported in versions 4 and up. Thank you. Is there a workaround for doing this kind of a query in < 4.1?

      -Sean

        You can get the effect of NOT IN or NOT EXISTS with a subselect by using a left join. I don't have a db convenient to work out a query, but it's something like this (using the fact that the left join returns null when there's no match):

        select colA 
from tableA
left join tableB
on tableA.id = tableB.id
where tableB.id is null
          Write a Reply...