Increment value in property with SQL

Question

Question

Dmitry Maslennikov · Mar 6, 2019

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'

Discussion (6)1

Log in or sign up to continue

Vitaliy Serdtsev · Mar 6, 2019

And if so?

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

0 0

score 0 · Answer 1 · 2019-03-06T15:58:43-05:00

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

score 0 · Answer 2 · 2019-03-06T17:54:38-05:00

Robert Cemper · Mar 6, 2019

This just doesn't work in Caché SQL

0 0

score 1 · Answer 3 · 2019-03-06T11:39:30-05:00

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

score 0 · Answer 4 · 2019-03-06T13:04:59-05:00

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

score 1 · Answer 5 · 2019-03-06T18:02:16-05:00

Robert Cemper · Mar 6, 2019

I think RowVersion and Serial Counter Fields should do it as well.

(with less typing)

1 0