Обновить одну таблицу на основе наивысшего значения записи в другой таблице в PostgreSQL.

На чтение 4 мин Опубликовано 21.10.2024

Содержание

Вопрос или проблема
Ответ или решение
Пример структуры таблиц:
SQL-запрос для обновления Table2
Пояснения к запросу:
Замечания по производительности и оптимизации:
Общие рекомендации:

Вопрос или проблема

У меня есть две таблицы (смотрите ниже) в базе данных postgreSQL. Таблица1 отслеживает все изменения, внесенные в каждый ID (т.е. несколько строк на ID), в то время как Таблица2 содержит только одну строку на ID. Мне нужен SQL-запрос для обновления Таблицы2 на основе наивысшего значения Record для каждого ID в Таблице1, а также обновления всех других полей (Data1, Data2, Data3 в этом примере). Эти таблицы являются всего лишь примерами, но мои реальные данные имеют много столбцов, поэтому я хотел бы использовать что-то вроде шаблона, чтобы включить все поля в обновление.

Я относительно нов в SQL в postgreSQL, но вот моя попытка получить последнюю версию записи, однако не уверен, как обновить Таблицу2 из этого. Я делал несколько попыток, но ничего не сработало, так что, вероятно, не стоит публиковать этот код.

  select * from
    Table1 t1
  where
    (ID,Record) in 
    (
      select
        ID,
        MAX(Record)
      from
        Table1 t1
      group by
        ID
     )

Таблица1:

Таблица2:

WITH hgh AS (
    SELECT  ID, MAX (Record) AS max_record
    FROM    Table1
    GROUP BY ID
)
UPDATE   Table2 t2
  SET Data1 = t1.Data1,
      Data2 = t1.Data2,
      Data3 = t1.Data3,
      Record = t1.Record
FROM hgh h
   JOIN Table1 t1
       ON (   t1.ID = h.ID
          AND t1.record = h.max_record
          )
WHERE  t2.ID = t1.ID

Ответ или решение

Чтобы обновить одну таблицу на основе максимального значения записи другой таблицы в PostgreSQL, необходимо использовать оператор UPDATE вместе с подзапросами и временными таблицами.

Рассмотрим две таблицы: Table1, которая содержит несколько строк на каждый ID с различными значениями записи, и Table2, где хранится только одна строка для каждого ID. Мы собираемся обновить Table2, основываясь на максимальном значении записи для каждого ID из Table1, и обновить все соответствующие поля.

Пример структуры таблиц:

Table1:
- ID
- Record
- Data1
- Data2
- Data3
Table2:
- ID
- Data1
- Data2
- Data3
- Record

SQL-запрос для обновления Table2

WITH max_records AS (
    SELECT 
        ID, 
        MAX(Record) AS max_record
    FROM 
        Table1
    GROUP BY 
        ID
)
UPDATE 
    Table2 t2
SET 
    Data1 = t1.Data1,
    Data2 = t1.Data2,
    Data3 = t1.Data3,
    Record = t1.Record
FROM 
    max_records mr
JOIN 
    Table1 t1 ON t1.ID = mr.ID AND t1.Record = mr.max_record
WHERE 
    t2.ID = t1.ID;

Пояснения к запросу:

CTE (Common Table Expression): Мы создаем временный набор данных max_records, который содержит уникальные ID из Table1 и соответствующее максимальное значение Record для каждого ID.
```
WITH max_records AS (
   SELECT 
       ID, 
       MAX(Record) AS max_record
   FROM 
       Table1
   GROUP BY 
       ID
)
```
Оператор UPDATE: Основная часть запроса обновляет данные в Table2. Мы используем SET для указания, какие столбцы должны быть обновлены:
- Data1, Data2, Data3 и Record будут обновлены на основе значений из Table1, где Record максимальный.
JOIN: Мы соединяем временную таблицу max_records с Table1 по условию, что совпадает и ID, и максимальное значение записи. Это позволяет получить необходимые данные для обновления.
WHERE: Условие WHERE гарантирует, что обновление происходит только для тех строк в Table2, которые соответствуют ID из Table1.

Замечания по производительности и оптимизации:

В данном запросе используется CTE для извлечения уникальных ID и их максимальных значений, что делает запрос более читаемым.
Необходимо убедиться, что индексы присутствуют на столбцах (ID, Record) для улучшения производительности выполнения запроса.

Общие рекомендации:

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

Такая структура запроса гарантирует, что Table2 будет точно обновлена значениями из Table1, основываясь на максимальном значении записи для каждого ID, обеспечивая целостность и актуальность данных.