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

    Do you have foreign key relationships properly defined? Seems to me like the 'ON DELETE CASCADE' parameter would automatically handle most or all of what you're trying to do.

      @

      No 🙁
      Database is already designed and do not have a change to set foreign keys...

        pedroz;10997888 wrote:

        not have a change to set foreign keys

        Why not?

        There will be lots of work to make sure that you really do remove everything which is supposed to be related in someone's mind while it isn't related at all in the database.

        Adding foreign keys is easy to do and when using ON DELETE CASCADE you ascertain that you cannot remove related material while the main relation is still in place, and when removing the main relation, all related stuff will also be deleted.

        To me it sounds like someone has made a decision along the lines of "We have an oil tanker with sails and need it to go around the world shipping goods. How can we make it go faster (note: we are not allowed to use engines)?

          johanafm;10997939 wrote:

          There will be lots of work to make sure that you really do remove everything which is supposed to be related in someone's mind while it isn't related at all in the database.

          And don't forget, you'll have to duplicate all of that work for every location throughout all PHP scripts that make such modifications.

          ... Or you could use foreign key constraints and let the DBMS take care of everything automatically (and consistently) for you.

            Write a Reply...