I have 3 tables!
Need a MYSQL delete query to delete rows in 3 tables at same time...
table1: log_visit
1) Need to DELETE from log_visit WHERE counter > 5
id | visit | visitor | counter
1 - 10 - qwerty | 15
2 - 11 - qwerty | 10
3 - 12 - azerty | 8
4 - 13 - azerty | 1
it will delete row 1 and 2
table2: log_link_visit_action
2) need to get visit and visitor rows deleted from table1 and delete in table2, in this case
[row 1-table1] DELETE from log_link_visit_action WHERE visit = 10 AND visitor= querty
[row 2-table1] DELETE from log_link_visit_action WHERE visit = 11 AND visitor=querty
[row 3-table1] DELETE from log_link_visit_action WHERE visit = 12 AND visitor=azerty
id | visit | visitor | url
15 - 8 - dodid - yahoo.com
16 - 10 - qwerty - google.com
17 - 11 - qwerty - abc.com
18 - 15 - kaz - zzz.com
it will delete row 16 and 17
table3: log_action
3) Need to delete all url deleted in table2, in this case
[row 17-table2] DELETE from log_action WHERE idaction = 'google.com'
[row 18-table2] DELETE from log_action WHERE idaction = 'abc.com'
id | idaction
1 - google.com
2 - yahoo.com
3 - abc.com
it will delete row 1 and 3
Any possible help joining delete queries in one query would be extremely helpful
Many thanks
3 tables delete query. mysql geek need :)
Moderator: General Moderators
Re: 3 tables delete query. mysql geek need :)
How about using InnoDB tables with cascading deletes?
Re: 3 tables delete query. mysql geek need :)
Celauran,
I thought about that, but mysql database is already designed with MyISAM and can't change it.
I thought about that, but mysql database is already designed with MyISAM and can't change it.
Re: 3 tables delete query. mysql geek need :)
are you sure that you can't?.... what kind of restrictions do you have?.... You would gain many benefits from changing over (rollback/commit, cascade on deletes/updates, transactions, etc), and remember, you don't have to change the whole database, you can select which tables will be served better for MyIsam or Innodb Storage Engines.but mysql database is already designed with MyISAM and can't change it.
otherwise... even when it is possible to do what you are asking for with a multi-delete sentence http://dev.mysql.com/doc/refman/5.0/en/delete.html I will rather suggest to use individual sentences for each table and control properly the execution and possible errors... this read could be interesting for you too http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html