clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36262 in the last week).

SELECT VERSION();
VERSION()
8.0.27
 hidden batch(es)


DROP TABLE IF EXISTS `pat_details`; DROP TABLE IF EXISTS `pats`; CREATE TABLE IF NOT EXISTS `pats` ( `id` BIGINT NOT NULL PRIMARY KEY, `patron_id` INT NOT NULL ); CREATE TABLE IF NOT EXISTS `pat_details` ( `id` BIGINT NOT NULL PRIMARY KEY, `pat_id` BIGINT NOT NULL, `dia_fin` SMALLINT NOT NULL, `mes_fin` SMALLINT NOT NULL, `anio_fin` SMALLINT NOT NULL, FOREIGN KEY (`pat_id`) REFERENCES `pats`(`id`) ); INSERT INTO `pats` VALUES (5569249, 13), (5569250, 13), (5569251, 13), (5569252, 13), (5569253, 13), (5569254, 13); INSERT INTO `pat_details` VALUES (4727792, 5569251, 1, 8, 2000), (4727793, 5569251, 1, 2, 2001), (4727794, 5569251, 1, 6, 2007), (4727795, 5569251, 1, 11, 2010), (4727796, 5569251, 1, 9, 2016), (4727797, 5569251, 2, 3, 2020), -- (4727798, 5569251, 16, 5, 2022); (4727798, 5569251, 1, 1, 2021); SELECT `id`, `patron_id` FROM `pats`; SELECT `id`, `pat_id`, `dia_fin`, `mes_fin`, `anio_fin` FROM `pat_details`;
id patron_id
5569249 13
5569250 13
5569251 13
5569252 13
5569253 13
5569254 13
id pat_id dia_fin mes_fin anio_fin
4727792 5569251 1 8 2000
4727793 5569251 1 2 2001
4727794 5569251 1 6 2007
4727795 5569251 1 11 2010
4727796 5569251 1 9 2016
4727797 5569251 2 3 2020
4727798 5569251 1 1 2021
 hidden batch(es)


UPDATE `pat_details` SET `dia_fin` = 16, `mes_fin` = 5, `anio_fin` = 2022 WHERE `id` IN ( SELECT MAX(`id`) FROM `pat_details` WHERE `pat_id` IN ( SELECT `id` FROM `pats` WHERE `patron_id` = 13 ) GROUP BY `pat_id` );
You can't specify target table 'pat_details' for update in FROM clause
 hidden batch(es)


SELECT `id`, `patron_id` FROM `pats`; SELECT `id`, `pat_id`, `dia_fin`, `mes_fin`, `anio_fin` FROM `pat_details`;
id patron_id
5569249 13
5569250 13
5569251 13
5569252 13
5569253 13
5569254 13
id pat_id dia_fin mes_fin anio_fin
4727792 5569251 1 8 2000
4727793 5569251 1 2 2001
4727794 5569251 1 6 2007
4727795 5569251 1 11 2010
4727796 5569251 1 9 2016
4727797 5569251 2 3 2020
4727798 5569251 1 1 2021
 hidden batch(es)


UPDATE `pat_details` INNER JOIN ( SELECT MAX(`id`) `id` FROM `pat_details` WHERE `pat_id` IN ( SELECT `id` FROM `pats` WHERE `patron_id` = 13 ) GROUP BY `pat_id` ) `max_pat_details_id_by_pat_id` ON `max_pat_details_id_by_pat_id`.`id` = `pat_details`.`id` SET `dia_fin` = 16, `mes_fin` = 5, `anio_fin` = 2022;
 hidden batch(es)


SELECT `id`, `patron_id` FROM `pats`; SELECT `id`, `pat_id`, `dia_fin`, `mes_fin`, `anio_fin` FROM `pat_details`;
id patron_id
5569249 13
5569250 13
5569251 13
5569252 13
5569253 13
5569254 13
id pat_id dia_fin mes_fin anio_fin
4727792 5569251 1 8 2000
4727793 5569251 1 2 2001
4727794 5569251 1 6 2007
4727795 5569251 1 11 2010
4727796 5569251 1 9 2016
4727797 5569251 2 3 2020
4727798 5569251 16 5 2022
 hidden batch(es)


WITH `max_pat_details_id_by_pat_id` AS ( SELECT MAX(`id`) `id` FROM `pat_details` WHERE `pat_id` IN ( SELECT `id` FROM `pats` WHERE `patron_id` = 13 ) GROUP BY `pat_id` ) UPDATE `pat_details` INNER JOIN `max_pat_details_id_by_pat_id` ON `pat_details`.`id` = `max_pat_details_id_by_pat_id`.`id` SET `dia_fin` = 1, `mes_fin` = 1, `anio_fin` = 2021;
 hidden batch(es)


SELECT `id`, `patron_id` FROM `pats`; SELECT `id`, `pat_id`, `dia_fin`, `mes_fin`, `anio_fin` FROM `pat_details`;
id patron_id
5569249 13
5569250 13
5569251 13
5569252 13
5569253 13
5569254 13
id pat_id dia_fin mes_fin anio_fin
4727792 5569251 1 8 2000
4727793 5569251 1 2 2001
4727794 5569251 1 6 2007
4727795 5569251 1 11 2010
4727796 5569251 1 9 2016
4727797 5569251 2 3 2020
4727798 5569251 1 1 2021
 hidden batch(es)