3 tables delete query. mysql geek need :)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pedroz
Forum Commoner
Posts: 99
Joined: Thu Nov 03, 2005 6:21 am

3 tables delete query. mysql geek need :)

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

How about using InnoDB tables with cascading deletes?
pedroz
Forum Commoner
Posts: 99
Joined: Thu Nov 03, 2005 6:21 am

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

Post by pedroz »

Celauran,

I thought about that, but mysql database is already designed with MyISAM and can't change it.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post 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
Post Reply