Обновление строк таблицы в Postgres с использованием подзапроса

Question

У меня есть таблица в базе данных Postgres 8.4:

CREATE TABLE public.dummy
(
  address_id SERIAL,
  addr1 character(40),
  addr2 character(40),
  city character(25),
  state character(2),
  zip character(5),
  customer boolean,
  supplier boolean,
  partner boolean
)
WITH (
  OIDS=FALSE
);

Я хочу обновить эту таблицу. Изначально я тестировал свой запрос, используя следующую команду insert:

insert into dummy (customer, supplier, partner)
SELECT  
    case when cust.addr1 is not null then TRUE else FALSE end as customer, 
    case when suppl.addr1 is not null then TRUE else FALSE end as supplier,
    case when partn.addr1 is not null then TRUE else FALSE end as partner
from (
    SELECT *
        from dummy) pa
    left outer join cust_original cust
        on (pa.addr1 = cust.addr1 and pa.addr2 = cust.addr2 and pa.city = cust.city 
            and pa.state = cust.state and substring(cust.zip, 1, 5) = pa.zip)
    left outer join supp_original suppl 
        on (pa.addr1 = suppl.addr1 and pa.addr2 = suppl.addr2 and pa.city = suppl.city 
                and pa.state = suppl.state and pa.zip = substring(suppl.zip, 1, 5))
    left outer join partner_original partn
        on (pa.addr1 = partn.addr1 and pa.addr2 = partn.addr2 and pa.city = partn.city
                  and pa.state = partn.state and pa.zip = substring(partn.zip, 1, 5))
where pa.address_id = address_id

Как мне преобразовать это в оператор update, то есть обновить существующие строки, используя значения, возвращаемые из оператора select?

Answer 1

Postgres позволяет использовать следующий синтаксис для обновления данных:

UPDATE dummy
SET customer = subquery.customer,
    address = subquery.address,
    partn = subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* большой SQL-запрос */ ...) AS subquery
WHERE dummy.address_id = subquery.address_id;

Этот синтаксис не является стандартным SQL, однако он значительно удобнее для таких типов запросов по сравнению со стандартным SQL. Я думаю, что Oracle (по крайней мере) также принимает что-то похожее.

Answer 2

Если нет прироста производительности при использовании соединений, то я предпочитаю использовать обобщенные таблицы (CTE) для повышения читаемости:

WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM /* большой и сложный SQL */ ...
)
UPDATE dummy
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE dummy.address_id = subquery.address_id;

На мой взгляд, это немного более современный подход.

Answer 3

Конечно, вот перевод на русский язык в стиле ответа на StackOverflow:

@Mayur "4.2 [Использование запроса с сложным JOIN]" с помощью Общие табличные выражения (CTE) мне очень помогли.

WITH cte AS (
SELECT e.id, e.postcode
FROM employees e
LEFT JOIN locations lc ON lc.postcode = e.postcode
WHERE e.id = 1
)
UPDATE employee_location SET lat = lc.lat, longitude = lc.longi
FROM cte
WHERE employee_location.id = cte.id;

Надеюсь, это поможет... 😄

Если у вас есть дополнительные вопросы, не стесняйтесь обращаться!

Answer 4

Этот SQL-запрос обновляет значение столбца isnullable в таблице json_source_tabcol на основе данных из представления information_schema.columns. В запросе используются следующие шаги:

Обновление таблицы: Используется команда UPDATE, чтобы изменить записи в таблице json_source_tabcol, сокращенно обозначенной как d.
Установка нового значения: Столбец isnullable в таблице json_source_tabcol устанавливается равным значению is_Nullable из таблицы information_schema.columns, обозначенной как a.
Условия выборки: Обновление происходит при условии, что название таблицы (table_name), схема таблицы (table_schema) и название столбца (column_name) совпадают между двумя таблицами d и a.

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

Обновление строк таблицы в Postgres с использованием подзапроса

4 ответ(ов)

Руководство по Markdown

Форматирование текста

Код

Списки

Поддерживаемые языки программирования

Популярные

Другие .NET

Базы данных