l4suicide — 2010-04-07T02:10:18-04:00 — #1
I'm trying to delete from 3 tables which share a primary key value for just one of these values
DELETE GAME_CONFIG_RULES,GAME_CONFIG_CIVILISATIONS,GAME_CONFIG_CIVILISATION_UNITS FROM GAME_CONFIG_RULES,GAME_CONFIG_CIVILISATIONS,GAME_CONFIG_CIVILISATION_UNITS WHERE GAME_CONFIG_CIVILISATIONS.game_config_id='2' OR GAME_CONFIG_RULES.game_config_id='2' OR GAME_CONFIG_CIVILISATION_UNITS.game_config_id='2'
This deletes 0 rows despite there being rows in 2 of these tables, but not the third.
Is it possible to do this in one query? Or do I just make 3 queries instead?
r937 — 2010-04-07T04:49:21-04:00 — #2
i would do it in 3 separate queries
what you have there is dangerous and as i read it (without actually testing it) it would delete all rows from all three tables as soon as one of those WHERE conditions is true
l4suicide — 2010-04-07T15:47:26-04:00 — #3
That's the whole point
I'm trying to delete all rows which have the same 'config_id' from those 3 tables.
It should only delete the rows with config_id=$POST[configid] - but the above only works if there are rows in each table with that id, and there may not always be rows in each table with the required id.
r937 — 2010-04-07T16:01:32-04:00 — #4
then use three separate DELETE queries
ibazz — 2010-04-07T19:16:18-04:00 — #5
Not sure what you mean but, is it that the records in the first table have a specific ID and in the other tables, the foreign key is the PK in table 1? Maybe this is where foreign key constraints set to ON DELETE CASCADE would delete the records in tables 2 and 3, where the match is made only on table 1?