Как использовать UPDATE с LEFT JOIN в PostgreSQL

Question

В MySQL можно обновлять таблицу с использованием `LEFT JOIN`, например:

```sql
UPDATE users 
LEFT JOIN topics ON users.id = topics.user_id 
SET users.author = 'no' 
WHERE topics.id IS NULL;
```

Это работает для проставления `'no'` всем пользователям, у которых нет топиков. В PostgreSQL такой синтаксис не поддерживается. Как корректно сделать аналогичное обновление в PostgreSQL, чтобы обновлять пользователей, которым нет соответствующих записей в другой таблице?

Nikolai Gagarinov · Answer

В PostgreSQL `UPDATE` с `JOIN` работает иначе. Синтаксис `UPDATE ... JOIN ... SET ...` не применяется. Вместо этого есть два подхода:

1. **Использовать подзапрос**

```sql
UPDATE users
SET author = 'no'
WHERE id NOT IN (SELECT user_id FROM topics);
```

* Здесь выбираются все пользователи, у которых нет записей в таблице `topics`.
* Этот вариант эквивалентен `LEFT JOIN ... WHERE ... IS NULL` в MySQL.

2. **Использовать `FROM` для соединения таблиц**

```sql
UPDATE users
SET author = 'yes'
FROM topics
WHERE users.id = topics.user_id;
```

* Это работает как `INNER JOIN` — обновляются только пользователи, у которых есть соответствующие записи в `topics`.
* Для реализации «LEFT JOIN» с обновлением пользователей без топиков можно комбинировать с условием:

```sql
UPDATE users
SET author = 'no'
FROM topics
WHERE users.id = topics.user_id IS NULL;
```

* Более универсально для сложных запросов часто используют **CTE (`WITH`)**, чтобы сформировать набор пользователей без топиков:

```sql
WITH no_topics AS (
  SELECT id FROM users
  LEFT JOIN topics ON users.id = topics.user_id
  WHERE topics.id IS NULL
)
UPDATE users
SET author = 'no'
WHERE id IN (SELECT id FROM no_topics);
```

* Этот способ создаёт временную таблицу `no_topics`, с которой удобно работать и делать обновление.

Таким образом, PostgreSQL не поддерживает прямой `UPDATE ... JOIN ...`, но можно использовать подзапросы, `FROM` и CTE для аналогичного результата.

Как использовать UPDATE с LEFT JOIN в PostgreSQL

Ответы