Исправляем ошибку удаления записей через INNER JOIN в PostgreSQL

Быстрый ответ

Чтобы удалить записи из таблицы в PostgreSQL, используя соответствие условиям в другой таблице, в команде DELETE применяется команда USING:

DELETE FROM target_table USING source_table
WHERE target_table.matching_column = source_table.matching_column;

В этом контексте target_table – это таблица, из которой необходимо удалить записи; source_table – таблица для сравнения, а matching_column – колонка, по которой связываются эти таблицы.

Работаете со старыми версиями PostgreSQL? Вот ваше решение

Если у вас старая версия PostgreSQL, например 8.2.11, то команда USING может быть недоступна. В таком случае можно использовать подзапросы с оператором IN как альтернативное решение:

DELETE FROM target_table
WHERE target_table_id IN (
  SELECT target_table_id FROM target_table
  JOIN source_table ON target_table.matching_column = source_table.matching_column
);

В данном случае подзапрос используется для выборки идентификаторов записей, которые необходимо удалить, а затем эти идентификаторы используются в операторе DELETE.

Чистота синтаксиса: ответ состоит в использовании псевдонимов

При работе со сложными операциями удаления, вовлекающими несколько таблиц, синтаксис можно упростить с помощью псевдонимов:

DELETE FROM target_table t
USING source_table s
WHERE t.id = s.target_table_id;

В данном случае t и s служат псевдонимами для target_table и source_table соответственно. Псевдонимы используются в условии WHERE для связывания таблиц по общему идентификатору.

Возникли проблемы? Попробуйте следующий подход

Если возникают ошибки при выполнении операций DELETE JOIN, следует воспользоваться следующими рекомендациями:

• Проверьте версию PostgreSQL: Убедитесь, что используемый вами синтаксис поддерживается вашей версией PostgreSQL.
• Пересмотрите условия соединения: Проверьте правильность формулировки условий в предложении WHERE.
• Обратитесь к сообществу: Не стесняйтесь просить помощи на форумах сообщества PostgreSQL.

Распространенные ошибки и способы их устранения

При работе с оператором DELETE учитывайте следующие особенности:

1. В версиях PostgreSQL до 9.1 не следует использовать конструкции с CTE при операциях удаления.
2. Всегда создавайте резервные копии данных.
3. Производите тестирование операций удаления в контролируемой среде перед их применением в реальной работе.

Визуализация

Представим, что на книжной ярмарке есть два раздела:

📚 Раздел A: Биографии (есть материалы о спорной персоне)
📚 Раздел B: Рецензии критиков (существуют рецензии на материалы о спорной персоне)

Требуется удалить все биографии спорной персоны, которые совпадают с рецензиями критиков.

DELETE FROM biographies b
USING critics_reviews c
WHERE b.controversial_figure_id = c.figure_id
  AND b.controversial_figure_id = <SpecificID>;

Визуальное представление:

До удаления: 📚👀📚 (Биографии сплетены с рецензиями)
После удаления: 📚    📚 (Остались только биографии о персонах, не привлекавших споров)

Используя DELETE с использованием INNER JOIN, мы удаляем только те биографии, которые соответствуют заданным условиям.

Точность при удалении данных

Не забывайте про следующие принципы при выполнении точных операций удаления:

• Перепроверьте условия соединения: Убедитесь, что условия в предложении WHERE точно соответствуют вашей задаче.
• Используйте подзапросы: Применяйте подзапросы для выделения записей, предназначенных к удалению.
• Следуйте лучшим практикам: Придерживайтесь проверенных практик использования SQL при работе с внутренними соединениями и операциями удаления.

Возможно ли множественное соединение? Да, это возможно!

При выполнении операций со множественными исходными таблицами, в операторе USING можно использовать запятые:

DELETE FROM target_table t
USING source_table_one s1, source_table_two s2
WHERE t.column1 = s1.column1
AND t.column2 = s2.column2;

В это случае в операторе USING указываются две разные исходные таблицы, что позволяет выполнять сложные операции удаления, принимая во внимание наличие множественных связей.

Полезные материалы

1. PostgreSQL: Documentation: 16: DELETE – официальная документация PostgreSQL по оператору DELETE.
2. Deleting data in many tables – PostgreSQL wiki – руководство по удалению данных из нескольких таблиц в PostgreSQL.
3. SQL: DELETE Statement – примеры и объяснения использования оператора DELETE в SQL с использованием соединений.
4. db<>fiddle – платформа для интерактивного тестирования SQL-оператора DELETE в сценариях с использованием внутреннего соединения.
5. How to delete many functions – Postgres OnLine Journal – обсуждение использования DELETE CASCADE в PostgreSQL для операций с несколькими таблицами.