CODEWITHSUNDEEP
phpmysql
starleaf1
starleaf1

Reputation: 2872

Deleting a single row from more than one tables in more than one databases

Here's the problem. I have a MySQL database that looks a little like this:

Table: db1.group_a
-----------
| name    |
-----------
| Alice   |
| Charlie |
-----------

Table: db2.group_b
----------
| name   |
----------
| Debbie |
| Bob    |
----------

(Yes, each table is in different databases. Also, the actual tables have similar but different structures, I just use one column for simplicity.)

Each name occurs only once in both databases.

Now what I need to do, is find a row (for example, Bob) and delete it from whichever table it's in. I'm thinking of deleting from group_a and then checking for affected row(s) and then doing some sort of if (affected_row < 1) { try_in_next_table(); } in php.

But it would be ideal to do this entirely in MySQL. Is there anyway to accomplish this?

Upvotes: 0

Views: 51

Answers (1)

Jevgenijs Vaikulis
Jevgenijs Vaikulis

Reputation: 686

Here are a few scenarios:

Bulk delete from all databases/tables:

DELETE FROM db1.group_a where name='Bob';
DELETE FROM db2.group_b where name='Bob';

OR Find actual db/table and then delete (php script):

$db = new mysqli("localhost", "my_user", "my_password", "world");
$tables = array('db1.group_a','db2.group_b');
foreach ($tables as $t)
  if ($query = $db->query("SELECT * from ".$t." WHERE name=".$db->escape_string("Bob")))
    if ($query->num_rows>0) {
      $db->query("DELETE FROM ".$t." WHERE name=".$db->escape_string("Bob"));
      break; // break foreach
    }

OR Delete without selecting:

$db = new mysqli("localhost", "my_user", "my_password", "world");
$tables = array('db1.group_a','db2.group_b');
foreach ($tables as $t)
  if ($query = $db->query("DELETE FROM ".$t." WHERE name=".$db->escape_string("Bob"))
    if ($query->affected_rows>0)
      break; // break foreach

Upvotes: 1

Related Questions