I'm trying (once again) to delete from multiple tables which are linked by the same primary id.
WHERE GAME_CONFIG_INITIAL_LAND.game_config_id=GAME_CONFIG_INITIAL_RESOURCES.game_config_id AND GAME_CONFIG_INITIAL_RESOURCES.game_config_id=GAME_CONFIG_INITIAL_BUILDINGS.game_config_id AND GAME_CONFIG_INITIAL_RESOURCES.game_config_id='1'
What I have works, but looking at the manual I think there is an easier way with the 'USING' clause. I just can't get it to work like the syntax suggests:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.] [, tbl_name[.]] ...
Am I reading this wrong?
Is there a quicker way of getting what I want done without so much garbage in my clause?
there are three ways
- delete individually from each table one at a time
- use cascading deletes (requires innodb and foreign keys)
- use a multi-table joined delete
you're working on the 3rd, and the part you're missing is that the word DELETE must be followed immediately by the word FROM (unless you want one of those optional keywords), and the USING clause is where you do the joins