UPDATE with ORDER BY

Question

Need to "tie" UPDATE with ORDER BY. I'm trying to use cursors, but get the error:

cursor "cursupd" doesn't specify a line,
SQL state: 24000

Code:

BEGIN;
    DECLARE cursUpd CURSOR FOR SELECT * FROM "table" WHERE "field" = 5760 AND "sequence" >= 0 AND "sequence" < 9 ORDER BY "sequence" DESC;
    UPDATE "table" SET "sequence" = "sequence" + 2 WHERE CURRENT OF cursUpd;
    CLOSE cursUpd;
COMMIT;

How to do it correctly?

UPDATE 1

Without cursor, when I do like this:

UPDATE "CableLinePoint" AS "t"
SET "sequence" = t."sequence" + 2
from (
    select max("sequence") "sequence", "id"
    from "CableLinePoint"
    where
        "CableLine" = 5760
    group by "id"
    ORDER BY "sequence" DESC
) "s"
where "t"."id" = "s"."id" and "t"."sequence" = "s"."sequence"

I get the unique error. So, need to update from the end rather than from the beginning.

UPDATE 2

Table:

id|CableLine|sequence
10|    2    |    1
11|    2    |    2
12|    2    |    3
13|    2    |    4
14|    2    |    5

Need to update (increase) the field "sequence". "sequence" have "index" type, so cannot be done:

UPDATE "table" SET "sequence" = "sequence" + 1 WHERE "CableLine" = 2

When "sequence" in the row with id = 10 is incremented by 1 I receive an error that another row with "sequence" = 2 already exists.

Please explain what you are trying to achieve. Is `id` the primary key? — Erwin Brandstetter, May 24 '13 at 14:56
This simplifies the query a lot (updated answer, workaround *not* in a single trasnaction!). The core problem with a non-deferrable constraint remains the same. — Erwin Brandstetter, May 24 '13 at 15:49

score 18 · Answer 1 · edited Feb 14 '17 at 14:44

18

UPDATE with 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 order is still random (I guess), but the values supplied to UPDATE command are matched by thetable.rowid=t1.rowid condition. So what I am doing is, first selecting the 'updated' table in memory, it's named t1 in the code above, and then making my physical table to look same as t1. And the update order does not matter anymore.

As for true ordered UPDATE, I don't think it could be useful to anyone.

edited Feb 14 '17 at 14:44

Athafoud

2,898
3
40
58

answered Apr 20 '15 at 12:22

alexkovelsky

3,880
1
27
21

is this really working in ordered (sorted) way? Can it be somehow proofed with some kind of logs or execution plan? – ALZ Feb 21 '16 at 16:31
I faced several time issues when same, or very similar `update` statement on many rows, executed parallel in different transactions, visits rows in different order and causing deadlock. – ALZ Feb 23 '16 at 07:51
Perhaps you could try: `CREATE thetable_copy AS TABLE thetable; UPDATE thetable SET columntoupdate=yourvalue FROM (SELECT rowid, 'thevalue' AS yourvalue FROM thetable_copy ORDER BY rowid) AS t1 WHERE thetable.rowid=t1.rowid;` – alexkovelsky Feb 23 '16 at 10:43

Erwin Brandstetter · Accepted Answer · 2022-05-02T06:13:02.977

`UPDATE` with `ORDER BY`

As to the question raised ion the title: There is no ORDER BY in an SQL UPDATE command. Postgres updates rows in arbitrary order. But you have (limited) options to decide whether constraints are checked after each row, after each statement or at the end of the transaction. You can avoid duplicate key violations for intermediate states with a DEFERRABLE constraint.

I am quoting what we worked out under this question:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

NOT DEFERRED constraints are checked after each row.
DEFERRABLE constraints set to IMMEDIATE (INITIALLY IMMEDIATE - which is the default - or via SET CONSTRAINTS) are checked after each statement.

There are limitations, though. Foreign key constraints require non-deferrable constraints on the target column(s).

The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.

Workaround

^{Updated after question update.}
Assuming "sequence" is never negative in normal operation, you can avoid unique errors like this:

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

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

With a non-deferrable constraint (default), you have to run two separate commands to make this work. Run the commands in quick succession to avoid concurrency issues. The solution is obviously not fit for heavy concurrent load.

Aside:
It's OK to skip the key word AS for table aliases, but it's discouraged to do the same for column aliases.

I'd advice not to use SQL key words as identifiers, even though that's allowed.

Avoid the problem

On a bigger scale or for databases with heavy concurrent load, it's wiser to use a serial column for relative ordering of rows. You can generate numbers starting with 1 and no gaps with the window function row_number() in a view or query. Consider this related answer:

Is it possible to use a PG sequence on a per record label?

FWIW, the lack of `ORDER BY` in `UPDATE ...` is a real PITA and makes it much harder to avoid deadlocks between queries concurrently updating the same table(s). Being able to specify a consistent locking order for `UPDATE` would be a big win for performance in some workloads - the sorting would cost, but not remotely as much as all the query retries. — Craig Ringer, May 26 '13 at 05:55

score 1 · Answer 3 · answered May 05 '14 at 19:27

Lazy Way, (aka not fastest or best 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;

Example

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

score 1 · Answer 4 · answered May 10 '19 at 06:43

1

Update with Order By

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;

answered May 10 '19 at 06:43

Syed Najam ul Hassan

11
4

Thanks for the concept. But your code doesn't work. whoever comes to find the code you can try from here. https://gist.github.com/JoshCheek/e19f83f271dc16d7825e2e4079538ba8 – ssi-anik Nov 17 '19 at 19:01
And update accordingly. Another issue with the above code is that the v+1, I don't think it'll work. I tried v = v+1. – ssi-anik Nov 17 '19 at 19:02

score 1 · Answer 5 · answered Nov 17 '19 at 19:13

If anyone comes here just like I came for the problem with rearranging the postgresql table_id_seq from 1 and order by the id. The solution I tried was partially taken from @Syd Nazam Ul Hasan (above) and https://gist.github.com/JoshCheek/e19f83f271dc16d7825e2e4079538ba8.

CREATE OR REPLACE FUNCTION update_sequence()
RETURNS SETOF varchar AS $$
DECLARE
  curs CURSOR FOR SELECT * FROM table ORDER BY id ASC;
  row  RECORD;
  v INTEGER := 0;
BEGIN
  open curs;
  LOOP
    FETCH FROM curs INTO row;
    update table 
    set id = v+1
    where id = row.id;
    v = v+1;
    EXIT WHEN NOT FOUND;
    return next row.id;
  END LOOP;
END; $$ LANGUAGE plpgsql;

SELECT update_sequence();

score 0 · Answer 6 · answered Mar 04 '16 at 20:45

This worked for me:

[update statement here] OPTION (MAXDOP 1) -- prevent row size from causing use of an eager spool, which mutilates the order in which records are updated.

I use a clustered int index in sequential order (generating one if needed) and hadn't had a problem until recently, and even then only on small rowsets that (counterintuitively) the query plan optimizer decided to use a lazy spool on.

Theoretically I could use the new option to disallow spool use, but I find maxdop simpler.

I am in a unique situation because the calculations are isolated (single user). A different situation may require an alternative to using maxdop limit to avoid contention.