Page 1 of 1

3 tables delete query. mysql geek need :)

Posted: Tue Feb 28, 2012 11:42 am
by pedroz
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

Re: 3 tables delete query. mysql geek need :)

Posted: Tue Feb 28, 2012 11:48 am
by Celauran
How about using InnoDB tables with cascading deletes?

Re: 3 tables delete query. mysql geek need :)

Posted: Tue Feb 28, 2012 1:56 pm
by pedroz
Celauran,

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 :)

Posted: Tue Feb 28, 2012 2:41 pm
by mikosiko
but mysql database is already designed with MyISAM and can't change it.
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.

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