DELETE and LEFT JOIN's

[smorb]

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.
Thx, Alex

[freakysid]

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.

[smorb]

Ok, so what could I do here:
I have this two tables:

[events]
event_id / location / ...

[date]
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.

[freakysid]

I'm not sure if I follow, but it would seem you have to do something like this. that's if I understand what you are trying to delete...

1) Do a SELECT query on table date to work out which events you need to delete from the database.
"
$sql = SELECT event_id FROM dates WHERE date= $yesterday";
$result = mysql_query($sql);

2) loop through this result set, and for each event that need to be deleted execute an SQL query to delete that event from the events table.

while ( $event = mysql_fetch_array($result)) {

$sql = "DELETE FROM events WHERE event_id = $event["event_id"]";
mysql_query($sql);
}

3) When that's all done, do a delete query to delete all rows for yesterday from your dates table.
$sql = "DELETE FROM date WHERE date = $yesterday;
mysql_query($sql);

I know its not simple or pretty - but like I said, this would all be avoided if MySQL supported foreign keys and cascade update and delete.

[smorb]

Yes, this helped me. Thank you

[smorb]

Still have a problem get it working. I get this error message:

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in ..... on line 22

where line 22 is this here:

$sql = "DELETE FROM events WHERE event_id = $event["event_id"]";

The rest works fine, I checked it.
Any idea, thanx, Alex

[freakysid]

Oh yeah, sorry, the quote marks around "event_id" cause a parse error on the string.

$sql = "DELETE FROM events WHERE event_id = $event[0]";

That will work.