SQLite:如何使用3个连接表进行更新?

Question

我的Plex服务器的元数据日期不正确。我试图将metadata_items.added_at设置为与其相关的media_parts.created_at，但您只能通过第三个表(media_items)找到关联。

下面是相关的表模式:

| media_parts                     |
| ------------------------------- |
| id | media_item_id | created_at |
| media_items           |
| --------------------- |
| id | metadata_item_id |
| metadata_items |
| -------------- |
| id | added_at  |

我可以使用3表连接获取信息:

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

但是我正在努力更新metadata_items表，因为你不能在UPDATE中使用JOIN。例如，我认为我想做以下事情:

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
)

但是它出错了。

我找到的所有例子都是解决方案，以取代一个2-表连接或设置一个常量值。

如何使用media_items将metadata_items与media_parts关联起来，用created_at的值更新added_at?

下面是一个被请求的sql转储，以重现一个最小的数据库:

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');

Answer 1

你可以使用SQLite的UPDATE…从语法:

UPDATE metadata_items AS mdi 
SET added_at = mp.created_at
FROM media_items AS mi INNER JOIN media_parts AS mp
ON mp.media_item_id = mi.id
WHERE mi.metadata_item_id = mdi.id;