micah1701 Posted October 3, 2006 Share Posted October 3, 2006 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] Quote Link to comment Share on other sites More sharing options...
shoz Posted October 3, 2006 Share Posted October 3, 2006 You can try this.[code]DELETEc1FROMcalendar AS c1LEFT JOIN calendar AS c2ON c1.id = c2.recurrenceOfWHEREc2.id IS NULLANDc1.date < '$saveDate';[/code]Btw, if you're storing dates in the database you can use the DATE type. Quote Link to comment Share on other sites More sharing options...
micah1701 Posted October 3, 2006 Author Share Posted October 3, 2006 thanks Shoz, that worked and got me on the right track to solve the rest of my issue. :D Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.