Удалить с помощью левого внешнего соединения в Postgres

Я перехожу базу данных из MySQL в Postgres SQL. Запрос выбора, который работал в MySQL, работает в Postgres, но аналогичного запроса на удаление нет.

У меня есть две таблицы данных, в которых перечислены некоторые резервные файлы. Существующие данные (ed) и новые данные (nd). Этот синтаксис выберет существующие данные, которые могут указывать, где находится файл в существующей таблице данных, сопоставляя его с одинаковым именем и пустым файлом, но не указав, где он находится в новых данных:

SELECT ed.id, ed.file_name, ed.cd_name, ed.path, nd.cd_name
FROM tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND 
ed.path = nd.path
WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

Я хочу запустить запрос на удаление с помощью этого синтаксиса:

DELETE ed
FROM tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND 
ed.path = nd.path
WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

Я пробовал DELETE ed и DELETE ed.*, оба из которых отображают syntax error at or near "ed". Подобные ошибки, если я пытаюсь без псевдонима ed. Если я попытаюсь выполнить

DELETE FROM tv_episodes AS ed
LEFT  JOIN data AS nd.....

Postgres отправляет обратно syntax error at or near "LEFT".

Я нахожусь в тупике и не могу много найти в запросах на удаление, используя объединения, специфичные для psql.

Ответы

Ответ 1

Как отмечали другие, вы не можете ВСПОМОГАТЬ JOIN непосредственно в инструкции DELETE. Тем не менее, вы можете присоединиться к первичному ключу к целевой таблице с помощью оператора USING, а затем присоединиться к этой самодостаточной таблице.

DELETE FROM tv_episodes
USING tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
   ed.file_name = nd.file_name AND 
   ed.path = nd.path
WHERE
   tv_episodes.id = ed.id AND
   ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

Обратите внимание на самостоятельное присоединение к tv_episodes.id в предложении WHERE. Это позволяет избежать описанного выше маршрута вспомогательного запроса.

Ответ 2

Вместо

DELETE ed
FROM tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND 
ed.path = nd.path
WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

попробуйте

DELETE FROM tv_episodes
WHERE cd_name = 'MediaLibraryDrive' AND 
  (tv_episodes.filename, tv_episodes.path IN
    (SELECT ed.filename, 
    ed.path 
    FROM tv_episodes AS ed 
    INNER JOIN data AS nd 
      ON ed.file_name = nd.file_name 
        AND ed.path = nd.path
    WHERE nd.cd_name IS NULL)
  )
  ;

JOIN недействителен в запросе DELETE в соответствии с документацией postgresql. Вам может понадобиться конкатенация левой и правой частей выражения IN.

Ответ 3

Как указывает bf2020, postgres не поддерживает JOINs при выполнении запроса DELETE. Предлагаемое решение подзапроса заставило меня задуматься над решением. Уточните запрос SELECT сверху и используйте его в качестве подзапроса для оператора запроса DELETE:

DELETE FROM tv_episodes 
WHERE id in (
    SELECT ed.id
    FROM tv_episodes AS ed
    LEFT OUTER JOIN data AS nd ON
    ed.file_name = nd.file_name AND 
    ed.path = nd.path
    WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL
);

Подзапросы часто могут быть неэффективным временем потребления и ресурсами ЦП с некоторыми системами баз данных, особенно с MySQL. По моему опыту я стараюсь избегать использования подзапроса из-за этой неэффективности плюс, что такие запросы иногда являются легким способом оттачивать один навык, например, изучать синтаксис JOIN.

Так как postgre не позволяет удалять запросы с помощью join, то это решение работает.