How to update field in table with datediff result

[BCJOHN70]

I have a simple ship tracking dbase with a couple of tables. I have arrival and departure date fields.
I also have a duration field to store the number of days between the 2 dates.
Under tools → SQL I have the following code to update a single row.
UPDATE “AnchorageUsage”
SET “DURATION” =
(SELECT DATEDIFF(‘day’, “ARRIVALDATE”,“DEPARTDATE”) FROM “AnchorageUsage” WHERE “ID” = ‘1’)
WHERE “ID” =‘1’

It is limited by the WHERE clause to limit the rows returned to 1.
How do I update all rows in the table, or have button on form to update the duration field when I enter the departure date?

Thanks in Advance. The simpler answer that the better, as I’m a newbie to LibreOffice.

[Villeroy]

If you can calculate the duration from the two times, there should be no need to store it.

[BCJOHN70]

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.

[Villeroy]

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 Sub