Increment value in property with SQL

Let's imagine you have only SQL. You need some table where you should increment value in some property when you update this line.

We have the table

CREATE TABLE "test" (
  "identifier"   VARCHAR(200) NOT NULL,
  "value"        INTEGER,
  PRIMARY KEY ("identifier")
)

One important note, you can't use INSERT and UPDATE queries separately, only as INSERT OR UPDATE. And CREATE TABLE is the only way to create the class.

INSERT OR UPDATE INTO "test" ("identifier", "value")
          VALUES ('someid', 1)

How to increment property value with each update?

It's not a problem to just update a row which already exists.

UPDATE  "test" 
SET "value" = "value" + 1
WHERE "identifier" = 'someid'

Answers

And if so?

CREATE TABLE test (
  identifier   VARCHAR(200NOT NULL,
  value        INTEGER COMPUTEONCHANGE("%%UPDATE"COMPUTECODE {&sql(select max(value)+1 into :{*} from test where identifier=:{identifier})},
  PRIMARY KEY (identifier)
)

I did not know about "%%UPDATE","%%INSERT". I decided to add an extra field

CREATE TABLE "test" (
  "identifier"   VARCHAR(200) NOT NULL,
  "value"        INTEGER
                 COMPUTECODE { Set {*}={*}+{increment} }
                 COMPUTEONCHANGE ("%%UPDATE","%%INSERT"),
  "increment"    INTEGER,
  PRIMARY KEY ("identifier")
)

and 

INSERT OR UPDATE INTO "test" ("identifier", "increment")
          VALUES ('someid', 1)

this solved my issue

Try this:

CREATE TABLE test (
  identifier   VARCHAR(200) NOT NULL,
  value        INTEGER COMPUTEONCHANGE("%%INSERT","%%UPDATE") COMPUTECODE { if %oper="INSERT" { set {*}=1 } elseif %oper="UPDATE" { set {*}={value}+1 } },
  PRIMARY KEY (identifier)
)

[SQL]SQL:USER>>select identifier, value from test

identifier value

 

0 Rows(s) Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 1')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 1')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 3')

1 Row Affected

[SQL]SQL:USER>>select identifier, value from test                 

identifier value

row 1     2    

row 2     3    

row 3     1    

 

3 Rows(s) Affected

 

DECLARE @IncrementValue int
SET @IncrementValue = 1
UPDATE Table1 SET Column1 = Column1 + @IncrementValue