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'
Comments
And if so?
<FONT COLOR="#0000ff">CREATE </FONT><FONT COLOR="#000080">TABLE </FONT><FONT COLOR="#008000">test </FONT><FONT COLOR="#000000">(
</FONT><FONT COLOR="#008000">identifier </FONT><FONT COLOR="#000080">VARCHAR</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">200</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">NOT NULL</FONT><FONT COLOR="#000000">,
</FONT><FONT COLOR="#008000">value </FONT><FONT COLOR="#000080">INTEGER COMPUTEONCHANGE</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"%%UPDATE"</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">COMPUTECODE </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">max</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">value</FONT><FONT COLOR="#000000">)+1 </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#800000">:{*} </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">test </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">identifier</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#800000">:{identifier}</FONT><FONT COLOR="#800080">)</FONT><FONT COLOR="#000000">},
</FONT><FONT COLOR="#000080">PRIMARY KEY </FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">identifier</FONT><FONT COLOR="#000000">)
)</FONT>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
This just doesn't work in Caché SQL
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 + @IncrementValueI think RowVersion and Serial Counter Fields should do it as well.
(with less typing)