Jump to content

Archived

This topic is now archived and is closed to further replies.

micah1701

MySQL error. Trying to DELETE using a subquery

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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.

Share this post


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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.