New post
Question
· Mar 6, 2019

Increment value in property with SQL

#SQL #Caché

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
Dmitry Maslennikov Mar 6, 2019 to Vitaliy Serdtsev

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

0 0
David Van De Griek · Mar 6, 2019

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

1 0