SQLite: How to update using 3 joined tables?-CodePudding

My Plex server's metadata has incorrect dates. I'm trying to set metadata_items.added_at to its associated media_parts.created_at, but you can only find the association through a 3rd table (media_items).

Here's the relevant table schemas:

| media_parts                     |
| ------------------------------- |
| id | media_item_id | created_at |

| media_items           |
| --------------------- |
| id | metadata_item_id |

| metadata_items |
| -------------- |
| id | added_at  |

I can get the information using a 3-table join:

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

But I'm struggling to update the metadata_items table since you can't use JOIN in an UPDATE. For example, I think I want to do the following:

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
)

But it errors.

All examples I'm finding are solutions to replace a 2-table join or are setting a constant value.

How do I associate metadata_items with media_parts using media_items to update added_at with the values from created_at?

Here's a sql dump as requested to reproduce a minimal db:

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

CodePudding user response：

You can use SQLite's UPDATE ... FROM syntax:

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;

See the demo.