Update table through row_number()-CodePudding

I've a table ANAGRAFICA in my db with 4 names: fabio, mrco, marco, mchele but mrco and mchele are incorrect (the column is NOME). I use this query to select a name through the row number:

SELECT NOME FROM 
   (SELECT NOME, ROW_NUMBER() OVER (ORDER BY (NULL)) R FROM ANAGRAFICA)
   WHERE R = 2;

and for example it gives me 'mrco'. Great,but how can i modify my query to update in order to correct that name on that particular row number?

CodePudding user response：

So say you; that's different in my database:

SQL> select nome,
  2         row_number() over (order by null) r
  3  from anagrafica;

NOME            R
------ ----------
mrco            1
fabio           2     --> 2 isn't "mrco" but "fabio"
marco           3 
mchele          4

SQL>

How come? Because ORDER BY NULL is as good as if there were no ORDER BY clause. In other words, those row numbers are useless; you could've used ROWNUM and get the same final result (useless).

How can you tell whether "fabio" is incorrect (or correct)? Or - OK, let it be "mrco" - what will you update its value to?

From my point of view, the only meaningful option is

SQL> update anagrafica set nome = 'marco' where nome = 'mrco';

1 row updated.

Anyway: if it were a problem that actually makes sense, rowid pseudocolumn might be useful. Here's how:

SQL> select nome,
  2         row_number() over (order by null) r,
  3         rowid
  4  from anagrafica;

NOME            R ROWID
------ ---------- ------------------
mrco            1 AAAF3xAAEAAAAuDAAA
fabio           2 AAAF3xAAEAAAAuDAAB
marco           3 AAAF3xAAEAAAAuDAAC
mchele          4 AAAF3xAAEAAAAuDAAD

SQL>

Use it in update as:

SQL> update anagrafica a set
  2    a.nome = 'little'
  3    where a.rowid = (select b.rwd
  4                     from (select row_number() over (order by null) r,
  5                                  rowid rwd
  6                           from anagrafica
  7                          ) b
  8                     where b.r = 2
  9                    );

1 row updated.

SQL> select * from anagrafica;

NOME
------
mrco
little
marco
mchele

SQL>

It updated row for which row_number analytic function returned value equal to 2. Once again - as you put it, you can't really tell which row it is (and certainly can't update it to a "valid" value because you don't know what you're updating so you can't specify a new value).