Update involving 3 tables

Navigation
3

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.

    
asked by anonymous 23.04.2015 / 17:36

2 answers

4

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.

    
23.04.2015 / 19:20
0

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"
    
23.04.2015 / 20:32
Get specific snippet of a [duplicate] string Convert to hexadecimal colors