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] Link to comment https://forums.phpfreaks.com/topic/22907-mysql-error-trying-to-delete-using-a-subquery/ 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. Link to comment https://forums.phpfreaks.com/topic/22907-mysql-error-trying-to-delete-using-a-subquery/#findComment-103343 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 Link to comment https://forums.phpfreaks.com/topic/22907-mysql-error-trying-to-delete-using-a-subquery/#findComment-103362 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.