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.

Solution 1

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 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?

Solution 2

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.

Solution 3

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;

Solution 4

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();

View more solutions