I want to store it, as it will be used for other calculations. i.e. min/max/avg/std.deviation, etc. and it will save re-calculating it every time I do a different statistic query.
I can only offer a macro to run SQL from a push button. Store the SQL code in the “additional info” field of a push button. Many statements can be separated by semicolon.
The macro will prompt for each semicolon separated snippet to be executed and reports how many records have been affected.
Mind that there is no undo for mass updates/edits/deletes.
If you are very sure that everything is well-formed, you may remove the Msgbox calls.
You can also use a view to select the wanted records, test the view thoroughly and then store a simple statement in the button’s additional info:
UPDATE "Table" SET "Column" = (SELECT "X" FROM "MyView" WHERE "XID" = "Table"."ID")
The macro:
Sub RunSQLButton(e)
Const cMaxLen = 1000
Const cTitle = "Command "
oModel = e.Source.Model
frm = oModel.getParent()
oCon = frm.ActiveConnection
aTags() = split(oModel.Tag, ";")
n = uBound(aTags)
for i = 0 to n
s = aTags(i)
sMsg = s
if len(s) > cMaxLen then sMsg = Left(s, cMaxLen) & CHR(10) &" [...]"
if len(s)>0 then
x = Msgbox(sMsg, 35, cTitle & i +1 &"/"& n +1 )
if x = 2 then exit sub 'Cancel
if x = 6 then'Yes
oStmt = oCon.prepareStatement(s)
on error goto errMsg
r = oStmt.executeUpdate()
Msgbox r &" records affected", 64, cTitle
endif
endif
next
exit sub
errMsg:
error(err)
End SubThx, I’ll give it a try.
I ended up with the same issue using the buttons additional info for the SQL. It requires a where clause to limit the result set to 1 row.
I found the answer in topic
’ How to set an item in a query to To Row number"
(Thanks to Doug)
I needed 2 cursors, one to select a subset of rows, and one to loop through the result-set and update the duration field.
UPDATE "AnchorageUsage" a
SET a.DURATION =
(SELECT DATEDIFF(day, b.ARRIVALDATE, b.DEPARTDATE)
FROM "AnchorageUsage" b
WHERE a.ID = b.ID)
Thx Carlos. This solution looks nice and simple. I’ll give it a try.
Take notice that if you are using the embedded HSQL database, I think identifiers must be put between " "
“a”.“DURATION” […]
Also check the exact sintaxe. This example is with Firebird.
Yes, I am using the embedded HSQL.
The only syntax required was single quotes around day.
Thx again, it works great!!!
@BCJOHN70,
although the accepted solution will work both the correlated subquery and WHERE clause are unnecessary.
all referenced fields reside in the same table therefore newbies please note that all you need to update the entire table is:
UPDATE "AnchorageUsage"
SET "DURATION" = DATEDIFF('day', "ARRIVALDATE","DEPARTDATE")
storing calculated values is usually considered a violation of normalisation.
1 Like
Wow! Always (of course!) learning here: didn’t know this!
By the way: after some issues, I have built an alternative form where the difference is inserted after updating the Departure Date column.
APPARENTLY, that works 
Another option to automate this would be the use of TRIGGER in the database itself: Following link shows an example of storing a timestamp on updates:
Of course. But user uses HSQL embedded and I don’t know if it have triggers 
I never use that engine.
It was an exercise about
How to update field in table with datediff result
thanks everyone for your assistance. I’ve learned multiple ways of doing things.
Has anybody used triggers? I have tried and keep getting syntax errors.
CREATE TRIGGER CALC_DURATION
AFTER UPDATE ON “AnchorageUsage”
FOR EACH ROW
BEGIN
SET “DURATION” = DATEDIFF(‘dd’,:ARRIVALDATE".“DEPARTDATE”)
END
OR
CREATE TRIGGER CALC_DURATION
AFTER UPDATE ON “AnchorageUsage”
REFERENCING NEW ROW AS bob FOR EACH ROW
BEGIN ATOMIC
SET “DURATION” = DATEDIFF(‘dd’,“ARRIVALDATE”,“DEPARTDATE”)
END
: " is an obvious problem in syntax
This works on Firebird.
It presupposes arrival date being inserted when ship docks.
CREATE TRIGGER SET_DURATION
ACTIVE BEFORE UPDATE POSITION 0
ON "AnchorageUsage"
AS
BEGIN
NEW.DURATION = DATEDIFF(DAY, OLD.ARRIVALDATE, NEW.DEPARTDATE);
ENDhi. what i uploaded was a re-type of the tools > sql window which included a couple of typos
for some reason I cannot cut or paste out of the tools > sql box. after i select text, then click copy it ignores the selection
@BCJOHN70,
you cannot use a trigger with an embedded HSQL database, use a split version and upgrade to HSQL 2.x at the same time.
.
assuming you use the default HSQL 1.8.10 and input your data via a form, a small macro may suffice.
here is a sample, the macro is fired by both “ARRIVALDATE” and “DEPARTDATE” on after update.
Ddiff.odb (13.7 KB)
just a reminder, you should not be storing calculated values!