Update involving 3 tables

Asked

Viewed 1,348 times

4

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 the table A in which the field FK_TABELA_B is equal to the largest ID of table B, but if the field FK_TABELA_C corresponds to the 'Cat' ID in table C, ie 22.

I have to use the term 'Cat' because in principle I do not know the 'Cat' ID, I used 22 only 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.

mysql

  • It’s complicated this...

  • @Jorgeb. is missing the relation with table C. It is necessary to update only the records of table A in which table C is equal to 'Cat', only that I have no relation between table A and C and table A with B and B with C, as in the example posted.

  • I got it, I deleted the comment.

2 answers

5


This is 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 to the Joao Araujo that solved this in Sqlserver.

  • 1

    To be just a cat just make one WHERE C.NAME ='Gato'

  • Boaaaaa! Worked perfectly with Where.

1

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 follows 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"

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.