How to update a table form itself?

Question

I've a table A. I want to increment a field by 1.

CREATE TABLE A(
a1 integer,
a2 integer
)

INSERT INTO A (a1, a2)
   VALUES (1, 1); 


update A ANEW set 
(a1,a2)=(select a1,a2+1 from A AOLD where ANEW.a1=AOWL.a1)

ORA-00904: "AOWL"."A1": invalid identifier

FORALL i IN INDICES of (select a1, a2 from A ) t
    update INTO A 
          VALUES (t(i).a1,t(i).a2+1)
          where A.a1=t(i).a1;

ORA-00900: invalid SQL statement

MERGE into A AOLD
using (select a1,a2+1 from A) ANEW on (ANEW.a1=aOLD.a1)
        WHEN MATCHED
        THEN
            UPDATE SET
                ANEW.a1=AOLD.a1,
                ANEw.a2=AOLD.a2+1;

ORA-00904: "ANEW"."A1": invalid identifier.

None of this solution is working

code

Answer 1

Your first attempt at the update will work successfully, you were just using the wrong alias. You were using AOWL instead of AOLD.

UPDATE A ANEW
   SET (a1, a2) =
           (SELECT a1, a2 + 1
              FROM A AOLD
             WHERE ANEW.a1 = AOLD.a1);

But the update can be simplified even more without needing to subquery

UPDATE a
   SET a2 = a2 + 1;