ОБНОВИТЬ с ORDER BY

Ответ 1

UPDATE с ORDER BY

Что касается вопроса, поднятого в заголовке: В команде SQL UPDATE нет ORDER BY. Postgres обновляет строки в произвольном порядке. Но у вас есть (ограниченные) варианты, чтобы решить, проверяются ли ограничения после каждой строки, после каждого оператора или в конце транзакции. Вы можете избежать повторяющихся нарушений ключа для промежуточных состояний с ограничением DEFERRABLE.

Я цитирую то, что мы разработали по этому вопросу:
Ограничение определено DEFERRABLE INITIALLY IMMEDIATE все еще откладывается?

• Ограничения NOT DEFERRED проверяются после каждой строки.

• Ограничения DEFERRABLE установленные на IMMEDIATE (INITIALLY IMMEDIATE или через SET CONSTRAINTS), проверяются после каждого оператора.

Есть ограничения, хотя. Ограничения внешнего ключа требуют неотложных ограничений на целевой столбец (столбцы).

Столбцы, на которые ссылаются, должны быть столбцами неотложного ограничения уникального или первичного ключа в таблице, на которую ссылаются.

Временное решение

^{Обновлено после обновления вопроса.}
Предполагая, что "sequence" никогда не бывает отрицательной в нормальной работе, вы можете избежать уникальных ошибок, таких как:

UPDATE tbl SET "sequence" = ("sequence" + 1) * -1
WHERE  "CableLine" = 2;

UPDATE tbl SET "sequence" = "sequence" * -1
WHERE  "CableLine" = 2
AND    "sequence" < 0;

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

В сторону:
Можно пропустить ключевое слово AS для псевдонимов таблиц, но не рекомендуется делать то же самое для псевдонимов столбцов.

Я бы посоветовал не использовать ключевые слова SQL в качестве идентификаторов, даже если это разрешено.

Избегайте проблемы

В больших масштабах или для баз данных с большой одновременной нагрузкой разумнее использовать serial столбец для относительного упорядочения строк. Вы можете генерировать числа, начинающиеся с 1 и без пробелов, с помощью оконной функции row_number() в представлении или запросе. Рассмотрите этот связанный ответ:
Можно ли использовать последовательность PG на каждом лейбле?

Ответ 2

UPDATE с ORDER BY:

UPDATE thetable 
  SET columntoupdate=yourvalue 
 FROM (SELECT rowid, 'thevalue' AS yourvalue 
         FROM thetable 
        ORDER BY rowid
      ) AS t1 
WHERE thetable.rowid=t1.rowid;

UPDATE порядок все еще случайный (я думаю), но значения, предоставленные команде UPDATE, соответствуют условию thetable.rowid=t1.rowid. Итак, что я делаю, сначала выбирая "обновленную" таблицу в памяти, она называлась t1 в приведенном выше коде, а затем моя физическая таблица выглядела так же, как t1. И порядок обновления больше не имеет значения.

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

Ответ 3

Lazy Way, (ака не самый быстрый или лучший способ)

CREATE OR REPLACE FUNCTION row_number(table_name text, update_column text, start_value integer, offset_value integer, order_by_column text, order_by_descending boolean)
  RETURNS void AS
$BODY$
DECLARE
    total_value integer;
    my_id text;
    command text;
BEGIN
total_value = start_value;
    command = 'SELECT ' || order_by_column || ' FROM ' || table_name || ' ORDER BY '  || order_by_column;

    if (order_by_descending) THEN
        command = command || ' desc';
    END IF;

    FOR  my_id in  EXECUTE command LOOP
        command = 'UPDATE ' || table_name || ' SET  ' || update_column || ' = ' || total_value || ' WHERE ' || order_by_column || ' = ' ||  my_id|| ';';

        EXECUTE command;
        total_value = total_value + offset_value;
    END LOOP;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Пример

SELECT row_number ('regispro_spatial_2010.ags_states_spatial', 'order_id', 10,1, 'ogc_fid', true)

Ответ 4

Это сработало для меня:

[обновить выражение здесь] ВАРИАНТ (MAXDOP 1) - предотвращает появление размера строки из-за использования требуемой катушки, которая искажает порядок обновления записей.

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

Теоретически я мог бы использовать новую опцию, чтобы запретить использование катушки, но я нахожу maxdop более простым.

Я в уникальной ситуации, потому что вычисления изолированы (один пользователь). В другой ситуации может потребоваться альтернатива использованию предела maxdop, чтобы избежать конкуренции.

Ответ 5

Declare 
v number;
cursor c1 is 
    Select col2 from table1 order by col2;
    begin
    v:=0;
     for c in c1
     loop
    update table1 
    set col1 =v+1
    where col2 = c.col2;
    end loop;
    commit;
    END;