SQL update records with ROW_NUMBER()

Question

I have a table called 'cards', which has a column called 'position' How can I update/set the 'position' to equal the row number of each record, using ROW_NUMBER()?

I am able to query the records and get the correct values using this statement:

"SELECT *,  ROW_NUMBER() OVER () as position FROM cards"

So, I would like to do this but have it update the new values in the database.

Answer 1

Let me assume that cards has a primary key. Then you can use join:

update cards c
    set position = c2.seqnum
    from (select c2.*, row_number() over () as seqnum
          from cards c2
         ) c2
    where c2.pkid = c.pkid;

I should note that the over () looks strange but Postgres does allow it. Normally an order by clause would be included.

Answer 2

Original question was tagged with SQLite. Starting from SQLite 3.25.0 we could natively use ROW_NUMBER.

CREATE TABLE cards(pk INT PRIMARY KEY, c VARCHAR(2), seq INT);
INSERT INTO cards(pk, c) VALUES (10,'2♥'),(20,'3♥'),(30, '4♥');

WITH cte AS (SELECT *, ROW_NUMBER() OVER() AS rn FROM cards)
UPDATE cards SET seq = (SELECT rn FROM cte WHERE cte.pk = cards.pk);

SELECT * FROM cards;

Exactly the same code will work with PostgreSQL too: Rextester Demo