Update value using row index-CodePudding

Say I have a table like below with values I want to update in specific rows, but no primary key, I only know the index of the row I want to update. Is this possible with generic SQL or would I need some DB specific tools? I'm using Postgres and SQLite. I realise this is bad DB design and the obvious solution is to simply add an id primary key to the table, but my use case is the DB is the backend for a flexible Excel-like application, where I have no control over the table schema, as it is user defined.

CREATE TABLE fruits (name TEXT);
INSERT INTO fruits VALUES (banana) (aple) (orange);

To fix the typo, I want to do something like:

UPDATE fruits SET name = 'apple' WHERE *row index* = 1;

Note I'm using 0-indexing in this pseudo code example.

CodePudding user response：

Did you try UPDATE fruits SET name='<new_name>' WHERE rowid=3 ?

rowid docs

CodePudding user response：

You could try

UPDATE fruits SET name = 'apple' WHERE name =(
    SELECT name FROM
       (SELECT name, row_number() over (order by name) as line_number FROM fruits )
    A 
    WHERE line_number = 1
)

So ROW_NUMBER is dynamically creating a unique number for each name, but it is doing so by sorting the results by name. So the index will be 1 if you want to select the 'Aple' entry.