Как обновить одно поле и вернуть всю сущность в одном операторе SQL в PostgreSQL?

Вот мой запрос:

UPDATE invoices SET
   version = o.version + 1
  ,modified = CURRENT_TIMESTAMP
  ,business_no = $3::numeric
FROM invoices o
LEFT JOIN reps ON reps.rep_id = o.rep_id
LEFT JOIN terms ON terms.terms_id = o.terms_id
LEFT JOIN shipvia ON shipvia.ship_via_id = o.ship_via_id
WHERE o.id = $1::int AND CASE WHEN $2::numeric IS NULL THEN o.business_no IS NULL ELSE o.business_no = $2::numeric END
RETURNING o.id, o.version, o.business_no, ..., terms, rep, ship_via

Он должен был сделать следующее:

  1. Найдите нужный счет-фактуру по его идентификатору ($1) и, имея ожидаемое значение в поле business_no ($2), установите для поля business_no новое значение ($3).
  2. Увеличьте поле версии на единицу.
  3. Вернуть все поля счета-фактуры после обновления.

К сожалению, это не работает, как ожидалось. Вместо того, чтобы обновлять только один счет и возвращать новую версию, он:

  1. Обновляет все счета
  2. Возвращает версию счета до обновления, дублированную N раз, где N — общее количество счетов.

Я понимаю, что мой запрос неверен, я просто не понимаю, почему и как это исправить.


postgresql sql
person mark    schedule 15.12.2013    source источник
comment
Вы не должны не включать целевую таблицу обновления в предложение FROM.   -  person a_horse_with_no_name    schedule 18.05.2018

Ответы (1)


arrow_upward
0
arrow_downward

Ваша проблема в том, что вы указываете счета-фактуры в списке FROM, не привязывая его к ОБНОВЛЯЕМОЙ таблице счетов-фактур. Об этом предупреждает руководство. Таким образом, простое исправление выглядит следующим образом:

   UPDATE invoices i
      SET version = i.version + 1,
          modified = CURRENT_TIMESTAMP,
          business_no = $3::numeric
     FROM           invoices o
          LEFT JOIN reps ON reps.rep_id = o.rep_id
          LEFT JOIN terms ON terms.terms_id = o.terms_id
          LEFT JOIN shipvia ON shipvia.ship_via_id = o.ship_via_id
    WHERE o.id = i.id
      AND o.id = $1::int
      AND (   (o.business_no IS NULL AND $2 IS NULL)
           OR (o.business_no = $2::numeric))
RETURNING i.id, i.version, i.business_no, i.modified, reps.name, terms.data, shipvia.who

У меня есть SQLFiddle, показывающий это с жестко закодированными значениями параметров.

Однако, поскольку вы, кажется, присоединяетесь к вторичным таблицам только ради предложения RETURNING, вы можете реорганизовать все это:

WITH o AS (
     UPDATE invoices
        SET version = version + 1,
            modified = CURRENT_TIMESTAMP,
            business_no = $3::numeric
      WHERE id = $1::int
        AND (   (business_no IS NULL AND $2 IS NULL)
             OR (business_no = $2::numeric))
  RETURNING *
) SELECT o.id, o.version, o.business_no, o.modified, reps.name, terms.data, shipvia.who
    FROM           o
         LEFT JOIN reps ON reps.rep_id = o.rep_id
         LEFT JOIN terms ON terms.terms_id = o.terms_id
         LEFT JOIN shipvia ON shipvia.ship_via_id = o.ship_via_id;

И обновленный SQLFiddle также показывает вторую версию. Я не могу сказать, лучше ли это в данном случае, но это альтернативный метод, который может быть полезен.

person gwaigh    schedule 15.12.2013
comment
Вы не должны не включать целевую таблицу обновления в предложение FROM. - person a_horse_with_no_name; 18.05.2018