Update in two columns, two tables

Question

I have to update two columns in different tables, but I don’t know the commands very well, I wouldn’t like to use triggers (triggers). Will be updated around 100 records.
_{Follow an example:}

_{Table a}

id    nomes  nota
1     SOen   etc1
2     SOpt   etc2

_{Table b}

id    nomes  outros
1     SOen   i
2     SOen   rre
3     SOpt   le
4     SOen   van
5     SOpt   te

That is, when I update a table Row a all Rows with the same table name a on the table b should be updated as well, with the same name changed in the table a.

_{Example of the desired update effect:}

Update a SET nomes = SOpt_BETAP WHERE nomes = SOpt

_{Table a} | Updated table with new names

id    nomes       nota
1     SOen        etc1
2     SOpt_BETA   etc2

_{Table b} | Table b is also updated with the new names

id    nomes       outros
1     SOen        i
2     SOen        rre
3     SOpt_BETA   le
4     SOen        van
5     SOpt_BETA   te

It would be beneficial for me and other users if I could make a brief explanation of each parameter used as ON, JOIN etc, explaining his function there, to those who also have doubts.

Answer 1

I think that it is not possible to do this with JOIN between the tables, because after the first UPDATE in one of the tables, the criterion of JOIN would no longer be met (example).

But I propose two alternatives:

Alternative 1: Foreign key in second table

If you refer to the Ids of table A in table B, instead of having the names repeated in the two tables, only table A would need to be updated. Table B would look like this:

id    a_id        outros
1     1           i
2     1           rre
3     2           le
4     1           van
5     2           te

Alternative 2: Make two UPDATEs distinct

I didn’t quite understand why you wanted to update the two tables in one operation. It would be much simpler to update each one separately:

UPDATE tabelaA SET nomes = 'SOpt BETA' WHERE nomes = 'SOpt';
UPDATE tabelaB SET nomes = 'SOpt BETA' WHERE nomes = 'SOpt';

Answer 2

I don’t like the idea anymore if it’s something already existing you can do the following. Assuming your bank is consistent.

you can add a single index to column nomes on the table a

alter table a add unique(nomes);

and a foreign key in the table b linking tables by columnnomes. adding the clause on update cascade you’re saying that when the column nomes in the parent table is updated to propagate the modifications.

alter table b add foreign key (nomes) references a(nomes) on update cascade;

So you will achieve your goal of updating the two tables with a single query.

Optionally you can also add the clause on delete cascade so that when the parent record is deleted automatically remove the child records, if you do not do this you will need to remove the child records explicitly before deleting the parent.

update a set nomes="zaz" where nomes="SOen"

see an example here on Sqlfiddle

Alternative

I decided to search a little and do some tests and managed to get the expected result with the following query.

update a, b set a.nome="zaz", b.nome="zaz" where a.nome="SOen" and b.nome="SOen";

However, it was necessary to inform the new values and values of the clause where explicitly.

Example in Sqlfiddle

Answer 3

To update the data from Table to and b follow suit:

Connection: PHP Data Objects (PDO)

<?php
    $pdo = new PDO('mysql:dbname=generics;host=localhost', 'root', 'senha', 
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

Where:

• PDO - Interface of access to several databases with PHP
  In its builder it has: string $dsn, string $username, string $password, array $driver_options. $dsn in case it went to Mysql: dbname is the name of the database and host may be the localhost or server ip database, $username is the bank user for connection, $password is the password of this user for connection and $driver_options are settings options for that connection. This link, has everything more detailed.

Code: with this connection we will use the following code to update the tables to and b

//ATUALIZANDO TABELA 'a'                    
$sts = $pdo->prepare('UPDATE a SET nomes=? WHERE nomes=?');
$sts->bindValue(1, 'SOpt_BETAP', PDO::PARAM_STR);
$sts->bindValue(2, 'SOpt', PDO::PARAM_STR);
$sts->execute();
$sts->closeCursor();
unset($sts);

//ATUALIZANDO TABELA 'b'                    
$sts = $pdo->prepare('UPDATE b SET nomes=? WHERE nomes=?');
$sts->bindValue(1, 'SOpt_BETAP', PDO::PARAM_STR);
$sts->bindValue(2, 'SOpt', PDO::PARAM_STR);
$sts->execute();
$sts->closeCursor();
unset($sts);       

$Pdo->prepare will prepare your SQL to receive the entrance vestments with bindValue following the order from left to right of the ? and having a defined type which in the case is String (PDO::PARAM_STR). In $Sts->execute() it will generate this SQL with values and update their tables.

That is to say,

<?php
    $pdo = new PDO('mysql:dbname=generics;host=localhost', 'root', 'senha', 
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

    //ATUALIZANDO TABELA 'a'                    
    $sts = $pdo->prepare('UPDATE a SET nomes=? WHERE nomes=?');
    $sts->bindValue(1, 'SOpt_BETAP', PDO::PARAM_STR);
    $sts->bindValue(2, 'SOpt', PDO::PARAM_STR);
    $sts->execute();
    $sts->closeCursor();
    unset($sts);
    //ATUALIZANDO TABELA 'b'                    
    $sts = $pdo->prepare('UPDATE b SET nomes=? WHERE nomes=?');
    $sts->bindValue(1, 'SOpt_BETAP', PDO::PARAM_STR);
    $sts->bindValue(2, 'SOpt', PDO::PARAM_STR);
    $sts->execute();
    $sts->closeCursor();
    unset($sts);

Just remembering that it would not be the best structure, IE, the tables are not normalized.