UpdateField and WHERE clause

Chate · 2022-03-06 04:47:28

Am trying to implement UpdateField on selected records in an SQLLite database. Have selected the required records with CreateAndFillPrepare then use the result, as a TSQLRecordClass, to Update a single field on many records.

The result is that all records in the DB are changed rather than just the ones selected. The declaration advises "Update one field in one or several members, depending on a WHERE clause".

May I ask how I insert that WHERE clause so the UpdateField works the way I want it to?

var
linksSelected: TSQLRecord;

.........

linksSelected :=
LinkMstr.CreateAndFillPrepare(ORM_Database, '(' +
ORMFLINKS_LINKLEVEL + '>=? AND ' + ORMFLINKS_LINKTGT + '>=?)',
[minlevel, minID]);

ORM_Database.UpdateField(linksSelected.RecordClass,
ORMFLINKS_URFLAGS, 0, ORMFLINKS_URFLAGS, 1);

Mormot declaration
function UpdateField(Table: TSQLRecordClass; const WhereFieldName: RawUTF8; const WhereFieldValue: array of const; const FieldName: RawUTF8; const FieldValue: array of const): boolean; overload; virtual;
Update one field in one or several members, depending on a WHERE clause

dcoun · 2022-03-06 16:09:45

I believe it works like the following:

UpdateField(linksSelected.RecordClass,'field1_to_match_in_where>=? AND  field2_to_match_in_where>=?',[field1_value,field2_value],'Field_to_update',[value_to_put_to_update_field])

I am not sure if the WhereFieldName can have more than one fields in the where expression in Mormot1

ab · 2022-03-06 18:17:26

Yes, dcount is right: CreateAndFillPrepare and UpdateField are uncorrelated.

You need to explicitly put the condition within the "where" clause of the UpdateField().

Chate · 2022-03-06 22:04:43

Many thanks for your assistance. Am currently running 1.18 so will update to Mormot2 if more than 1 fields not supported.

Chate · 2022-03-07 02:11:25

Have determined only one field may be in the Where expression in both 1.18 and 2. Handy if all records with a single condition are to be changed however I need to have multiple conditions so will revert to CreateAndFillPrepare and loop through the selected results. Was hoping UpdateField would handle multiple conditions as it would be so much faster than looping.

Have also determined that the Where condition does not need the >=? either it just needs the field name.

implementation of UpdateField with arrays starts with:

if (length(FieldValue)<>1) or (WhereFieldName='') or (length(WhereFieldValue)<>1) then
exit;

ab · 2022-03-07 07:22:36

You are right: there is no such WHERE clause here.

So a dual Retrive + UpdateField(IDs: array of TID) is the way to go here.

Chate · 2022-03-07 08:55:05

Had considered that approach but since there could be many affected records, 624 in one case of the DB I am currently working with have not proceeded. Have resolved by using the Execute method and inserting the SQL, works exactly as I need.

Thanks for a great product.

ab · 2022-03-07 09:11:20

Our ORM is not fully featured, mainly because it is not tied to SQL, but also works with NoSQL like MongoDB or our in-memory engine, so the ORM methods are not 100% aligned to the SQL possibilities.
In practice, the limitation to a single field location for update comes from the current state of our in-memory engine.

So yes, fallback to direct SQL is the way to go for such dedicated cases.

mORMot Open Source

#1 2022-03-06 04:47:28

UpdateField and WHERE clause

#2 2022-03-06 16:09:45

Re: UpdateField and WHERE clause

#3 2022-03-06 18:17:26

Re: UpdateField and WHERE clause

#4 2022-03-06 22:04:43

Re: UpdateField and WHERE clause

#5 2022-03-07 02:11:25

Re: UpdateField and WHERE clause

#6 2022-03-07 07:22:36

Re: UpdateField and WHERE clause

#7 2022-03-07 08:55:05

Re: UpdateField and WHERE clause

#8 2022-03-07 09:11:20

Re: UpdateField and WHERE clause

Board footer