Hi, I have an SQL Query where I use a LEFT JOIN. It works fine. But if I change it from "SELECT .. FROM" into "DELETE FROM" I get an ugly error message. Is it not possible to use LEFT JOINS together with "DELETE" ?
If not, what else could I do to delete staff which is in two joined tables.
This is one of the many things missing from MySQL - cascade updates and deletes. Most RDMS allow you to define whether cascade updates and deletes should be performed on tables which have a foreign key to another table. But there is no foreign key support in MySQL at this time. The only way I know is to execute seperate sql queries. This is a very poor situation as it can easily corrupt your data if the programmer forgets this or forgets or is not aware of the overall database schema. I would be pleasently suprised if someone can correct me on this or show an easier method.
Ok, so what could I do here:
I have this two tables:
event_id / location / ...
date / event_id
So I tried this here:
DELETE FROM date, events WHERE date.date=$yesterday AND date.event_id=events.event_id
but it doesn't work too. I can not make several querys here as for the "DELETE FROM events" query I don't know what to delete, as this information (the date of the event) is in the [date] table.