How to update a table form itself?-CodePudding

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

CodePudding user response：

You've mixed up your syntax on the merge statement. Try this (note that I've relabelled the aliases as "tgt" and "src" to help you understand where the columns are coming from):

MERGE into A tgt
using (select a1,
              a2 1 a2_new from A) src
  on (tgt.a1=src.a1)
WHEN MATCHED THEN
UPDATE SET tgt.a2=src.a2_new;

Note that I gave the a2 column in your source query an alias; you can now use that in the update part. Also, since you can't update a column that's in the join condition, I've removed that from the update part.

However, if all you're wanting to do is add 1 to the value of a column, why not just:

update a
set    a2 = a2   1;

db<>fiddle

CodePudding user response：

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;