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.
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.
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
Consider sequence solution as well. From https://dba.stackexchange.com/a/303832/58609
create sequence position_seq;
update cards
set position = nextval('position_seq');
drop sequence position_seq;
row_Number(), so the question only makes sense for Postgres.