Jump to content


Photo

MySQL error. Trying to DELETE using a subquery


  • Please log in to reply
2 replies to this topic

#1 micah1701

micah1701
  • Members
  • PipPipPip
  • Advanced Member
  • 613 posts
  • LocationEllington, CT USA

Posted 03 October 2006 - 07:00 PM

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:
SELECT * FROM calendar AS rowData 
WHERE startDateStamp < '$saveDate'
AND 
 ( SELECT COUNT(*) FROM calendar 
    WHERE recurrenceOf = rowData.id
    AND startDateStamp > '$saveDate'
  ) = 0
BUT when I change "SELECT * FROM" to "DELETE FROM" it fails and throws a syntax error.

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?

"Confidence in the face of risk."

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 03 October 2006 - 08:11 PM

You can try this.
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';

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

#3 micah1701

micah1701
  • Members
  • PipPipPip
  • Advanced Member
  • 613 posts
  • LocationEllington, CT USA

Posted 03 October 2006 - 08:40 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users