Update involving 3 tables

I have 3 tables:

TABELA A
ID   ID_TABELA_B
1    188
2    189   
3    190
4    200

TABELA B
ID    ID_TABELA_C
188   22
189   22   
190   22
200   23

TABELA C
ID   NAME
22   Gato
23   Cão

Table A is linked to table B and table B linked to table C as you can see through the field FK_TABELA_ [x].

It is necessary to update table A where the FK_TABELA_B field is equal to the largest ID of table B, but if the FK_TABELA_C field matches the ID of the ‘Cat’ in table C, that is 22.

I have to use the term ‘Cat’ because in principle I do not know the ‘Cat’ ID, I used 22 just for example.

The result after the query would be:

TABELA A
ID   FK_TABELA_B
1    190
2    190   
3    190
4    200

The above result is because “190” is the largest ID in table B with the “Cat” ID in table C.

Is this what you want?

UPDATE A INNER JOIN B ON A.FK_TABELA_B = B.ID 
         INNER JOIN C ON B.FK_TABELA_C = C.ID 
         INNER JOIN (SELECT FK_TABELA_C, MAX( ID ) AS idMax
                     FROM B
                     GROUP BY FK_TABELA_C)
                    T ON C.ID = T.FK_TABELA_C
SET A.FK_TABELA_B = T.idMax;

Credits for the Joao Araujo that solved this in SQLServer.

I’ll demonstrate in a simpler way without these INNER JOIN, GROUP BY etc.

CREATE TABLE 'test'.'tab_a' (
'id' INT NOT NULL AUTO_INCREMENT,
'tab_b_id' INT NULL,
PRIMARY KEY ('id'));

CREATE TABLE 'test'.'tab_b' (
'id' INT NOT NULL AUTO_INCREMENT,
'tab_c_id' INT NULL,
PRIMARY KEY ('id'));

CREATE TABLE 'test'.'tab_c' (
'id' INT NOT NULL AUTO_INCREMENT,
'name' VARCHAR(255) NULL,
PRIMARY KEY ('id'));

INSERT INTO 'test'.'tab_c' ('id', 'name') VALUES ('22', 'GATO');
INSERT INTO 'test'.'tab_c' ('id', 'name') VALUES ('23', 'CAO');

INSERT INTO 'test'.'tab_b' ('id', 'tab_c_id') VALUES ('188', '22');
INSERT INTO 'test'.'tab_b' ('id', 'tab_c_id') VALUES ('189', '22');
INSERT INTO 'test'.'tab_b' ('id', 'tab_c_id') VALUES ('190', '22');
INSERT INTO 'test'.'tab_b' ('id', 'tab_c_id') VALUES ('200', '23');

INSERT INTO 'test'.'tab_a' ('tab_b_id') VALUES ('188');
INSERT INTO 'test'.'tab_a' ('tab_b_id') VALUES ('189');
INSERT INTO 'test'.'tab_a' ('tab_b_id') VALUES ('190');
INSERT INTO 'test'.'tab_a' ('tab_b_id') VALUES ('200');

  

The update for this case is below

update 
    tab_a a,
    tab_b b,
    tab_c c
    set a.tab_b_id=(select max(aux.id) from tab_b aux where aux.tab_c_id=c.id ) 
    where
        a.tab_b_id = b.id and
        b.tab_c_id = c.id and
        c.name = "GATO"