Subquery in update doesn't see already updated records

I have a few thousand records with a duplicate sortorder (which causes duplicate entries in other queries), so I'm trying to set a correct sort order for all those records.

First I set them all to -1 so the sortorder would start from 0, and then I execute this query:

UPDATE op.customeraddress SET sortorder = (SELECT MAX(ca.sortorder) + 1 
                                           FROM op.customeraddress AS ca 
                                           WHERE ca.customerid = customeraddress.customerid) 
WHERE id IN (<subquery for IDs>)

The problem is that the MAX() in the subquery always seems to return the same value - it doesn't know about an earlier update.

The query works fine if I manually apply it record by record.

Any ideas on how to do this without having to resort to looping?

Answers


This should do it:

with new_order as 
(
   select ctid as rid,
          row_number() over (partition by customerid order by sortorder) as rn
   from customeraddress
) 
update customeraddress ca
  set sortorder = new_order.rn 
where ca.ctid = new_order.rid;
  and ca.id IN (<subquery for IDs>);

No need to reset the sortorder before running this, it will renumber all customeraddresses for a one customerid according to the old order.

You need PostgreSQL 9.1 for the above solution (writeable CTEs)

For previous version this should do it:

update customeraddress ca
   set ca.sortorder = t.sortorder
from 
(
   select ctid as rid,
          row_number() over (partition by customerid order by sortorder) as rn
   from customeraddress
)  t
where ca.ctid = t.rid
  and ca.id IN (<subquery for IDs>);

You could use a sequence:

CREATE TEMPORARY SEQUENCE sort_seq;
UPDATE op.customeraddress SET sort_order = (
     SELECT nextval('sort_seq')
     FROM op.customeraddress AS ca 
     WHERE ca.customerid = customeraddress.customerid
) WHERE id IN ...

Need Your Help

In-Memory user defined table, not in memory?

sql-server user-defined-types in-memory sql-server-2014 memory-optimized-tables

I am using SQL Server 2014 CTP2, with READ_COMMITTED_SNAPSHOT ON (I think it's important for the question).