MySQL error. Trying to DELETE using a subquery

[micah1701]

I have a table called "Calendar"
some of the fields are:

+-----+-------------+-----------+
|  id  | recurrenceOf |  date      |
+-----+-------------+-----------+
|  1  |                  | 20050603 |
+-----+-------------+-----------+
|  2  |      1          | 20050604 |
+-----+-------------+-----------+
|  3  |      1          | 20050605 |
+-----+-------------+-----------+

I want to delete all rows before a given date except, I do NOT want to delete a row that has other rows linked to it.

so, in the above example, I want to keep row 1 but delete the other two rows.

This works to return the correct results that I want to delete:
[code]
SELECT * FROM calendar AS rowData
WHERE startDateStamp < '$saveDate'
AND
( SELECT COUNT(*) FROM calendar
    WHERE recurrenceOf = rowData.id
    AND startDateStamp > '$saveDate'
  ) = 0
[/code]
BUT when I change "SELECT * FROM" to "DELETE FROM" it fails and throws a syntax error.

[b]EDIT:
"its okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table for both the subquerys FROM clause and the update target."

GREAT, SO ANY OTHER IDEAS HOW I CAN DO THIS IN ONE QUERY?[/b]

[shoz]

You can try this.
[code]
DELETE
c1
FROM
calendar AS c1
LEFT JOIN
calendar AS c2
ON c1.id = c2.recurrenceOf
WHERE
c2.id IS NULL
AND
c1.date < '$saveDate';
[/code]

Btw, if you're storing dates in the database you can use the DATE type.

[micah1701]

thanks Shoz, that worked and got me on the right track to solve the rest of my issue.
:D