How UpdateDataTableEntriesWithQuery works ?

[iguerra]

Has someone used this function ?

I need to update datatable entries, but with a query because I have to udpate just records where an integer  field is above a certain value ....

[PaiChung]

It basically works as you desire.

You can define the field value you want to update and define the query and it should update the records that match the query.

My go to for creating queries is here:

http://support.ptc.com/help/thingworx_hc/thingworx_8_hc/en/index.html#page/ThingWorx%2FHelp%2FComposer%2FThings%2FThingServices%2FQueryParameterforQueryServices.html

[seanccc]

@iguerra ,

I tested it days ago and found the API will query the full entry and update EVERY field of the entry , instead of ONLY the field to be updated. 

If you use PostgreSQL  and execute the statement. ( "ConCurrentDataTable" has lots of fields besides PropA )

var findValues =  Things["ConCurrentDataTable"].CreateValuesWithData({
    values:  {Type: 'IPhone'} /* JSON */
});

var updateValues =  Things["ConCurrentDataTable"].CreateValuesWithData({
    values:  {PropA: 'a'} /* JSON */
});

 Things["ConCurrentDataTable"].UpdateDataTableEntriesWithQuery({
    sourceType: undefined /* STRING */,
    values: findValues /* INFOTABLE */,
    query: undefined /* QUERY */,
    location: undefined /* LOCATION */,
    source: undefined /* STRING */,
    updateValues: updateValues /* INFOTABLE */,
    tags: undefined /* TAGS */
});

then you will see the following SQL statements once opening the SQL trace: 

SELECT * FROM data_table WHERE entity_id = $1 AND $2 <@ field_values ORDER BY entry_id ASC LIMIT 10000","parameters: $1 = 'ConCurrentDataTable', $2 = '{""PropA"": ""aa""}'

SELECT * FROM data_table WHERE entity_id = $1 AND $2 <@ field_values ORDER BY entry_id ASC LIMIT 10000 OFFSET 10000","parameters: $1 = 'ConCurrentDataTable', $2 = '{""PropA"": ""aa""}'

SELECT * FROM data_table where entity_id = $1 and entity_key = $2","parameters: $1 = 'ConCurrentDataTable', $2 = 'IPhone'

UPDATE data_table SET (entity_id, field_values, entity_key, location, source_id, source_type, tags, time, full_text) = ($1, $2, $3, $4, $5, $6, $7, $8, $9) WHERE entry_id = $10","parameters: $1 = 'ConCurrentDataTable', $2 = '{""Type"": ""IPhone"", ""PropA"": ""aabb"", ""PropB"": ""b2"", ""PropC"": ""c2"", ""PropD"": ""d"", ""PropE"": ""e2"", ""PropF"": ""f"", ""Counter"": 324, ""UpdateTime"": 1563338499132}', $3 = 'IPhone', $4 = '0.0,0.0,0.0', $5 = 'Administrator', $6 = 'User', $7 = '[]', $8 = '2019-07-24 11:09:50.685+08', $9 = 'aabb IPhone 324.0 b2 2019-07-17T12:41:39.132+08:00 c2 d e2 f', $10 = '3125716'",,,,,,,,"Postgr
00,"execute <unnamed>: UPDATE data_table SET (entity_id, field_values, entity_key, location, source_id, source_type, tags, time, full_text) = ($1, $2, $3, $4, $5, $6, $7, $8, $9) WHERE entry_id = $10","parameters: $1 = 'ConCurrentDataTable', $2 = '{""Type"": ""IPhone"", ""PropA"": ""aabb"", ""PropB"": ""b2"", ""PropC"": ""c2"", ""PropD"": ""d"", ""PropE"": ""e2"", ""PropF"": ""f"", ""Counter"": 324, ""UpdateTime"": 1563338499132}', $3 = 'IPhone', $4 = '0.0,0.0,0.0', $5 = 'Administrator', $6 = 'User', $7 = '[]', $8 = '2019-07-24 11:09:50.685+08', $9 = 'aabb IPhone 324.0 b2 2019-07-17T12:41:39.132+08:00 c2 d e2 f', $10 = '3125716'

[PaiChung]

Not sure unfortunately, I've done this with before or maybe with DeleteQuery and that worked fine.

[iguerra · Accepted solution]

was able to run the service correctly

followed this  https://www.ptc.com/it/support/article/CS214925 even if it is not written so clear.

It was important to use values and values2 for the fields that has to be updated:

values and query are used just to search the rows to be updated.

values2 specify the fields that will be to be updated (for all the rows found matching "values" and "query").

CIAO!

[CarlesColl]

Hi,

Just to clarify about your Updates All Fields sentence, yes it's how it works. Internally TW DataTables columns are all stored as one column (JSON values) that's why it always updates all columns, you always must provide the full set of values when updating TW DataTable entries.

Best Regards,

Carles