SQLite如何通过三张关联表实现UPDATE操作？

我的Plex服务器元数据日期存在错误，需要将metadata_items.added_at字段的值替换为关联的media_parts.created_at字段的值，这两个表需通过media_items表建立关联。

关联表结构 #

可正常执行的关联查询（验证数据对应关系） #

SELECT
    media_parts.created_at AS media_created_at,
    metadata_items.added_at AS metadata_added_at
FROM media_parts
INNER JOIN media_items
ON media_parts.media_item_id = media_items.id
INNER JOIN metadata_items
ON media_items.metadata_item_id = metadata_items.id

错误的更新尝试 #

之前尝试的UPDATE语句因子查询未关联当前行导致报错：

UPDATE metadata_items
SET added_at = (
    SELECT
        media_parts.created_at
    FROM media_parts
    INNER JOIN media_items
    ON media_parts.media_item_id = media_items.id
    INNER JOIN metadata_items
    ON media_items.metadata_item_id = metadata_items.id
)

正确的三表关联更新语句 #

在SQLite中，可通过带关联条件的子查询实现三表关联更新，确保子查询只返回当前行对应的created_at值：

UPDATE metadata_items
SET added_at = (
    SELECT mp.created_at
    FROM media_parts mp
    JOIN media_items mi ON mp.media_item_id = mi.id
    WHERE mi.metadata_item_id = metadata_items.id
)
-- 可选：仅更新存在对应关联数据的行，避免无关联行被设为NULL
WHERE EXISTS (
    SELECT 1
    FROM media_parts mp
    JOIN media_items mi ON mp.media_item_id = mi.id
    WHERE mi.metadata_item_id = metadata_items.id
);

复现问题的SQL Dump #

CREATE TABLE media_parts(
   id            INTEGER  NOT NULL PRIMARY KEY 
  ,media_item_id INTEGER  NOT NULL
  ,created_at    VARCHAR(19) NOT NULL
);
INSERT INTO media_parts(id,media_item_id,created_at) VALUES (33,33,'2016-05-13 19:59:06');
INSERT INTO media_parts(id,media_item_id,created_at) VALUES (44,44,'2015-10-12 02:15:21');
INSERT INTO media_parts(id,media_item_id,created_at) VALUES (72,72,'2016-01-01 02:13:58');
INSERT INTO media_parts(id,media_item_id,created_at) VALUES (118,118,'2016-03-05 19:57:32');

CREATE TABLE media_items(
   id               INTEGER  NOT NULL PRIMARY KEY 
  ,metadata_item_id INTEGER  NOT NULL
);
INSERT INTO media_items(id,metadata_item_id) VALUES (33,34);
INSERT INTO media_items(id,metadata_item_id) VALUES (44,45);
INSERT INTO media_items(id,metadata_item_id) VALUES (72,73);
INSERT INTO media_items(id,metadata_item_id) VALUES (118,117);

CREATE TABLE metadata_items(
   id       INTEGER  NOT NULL PRIMARY KEY 
  ,added_at VARCHAR(19) NOT NULL
);
INSERT INTO metadata_items(id,added_at) VALUES (34,'2019-05-13 20:00:54');
INSERT INTO metadata_items(id,added_at) VALUES (45,'2018-10-12 02:19:14');
INSERT INTO metadata_items(id,added_at) VALUES (73,'2019-01-01 02:42:43');
INSERT INTO metadata_items(id,added_at) VALUES (117,'2019-03-07 20:58:50');

内容的提问来源于stack exchange，提问作者oats