Skip to main content
I
iguerra16-Pearl
16-Pearl
July 30, 2019
Solved

How UpdateDataTableEntriesWithQuery works ?

  • July 30, 2019
  • 3 replies
  • 2935 views

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 ....

 

Best answer by iguerra

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!

 

3 replies

P
PaiChung22-Sapphire I
22-Sapphire I
July 30, 2019

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

    S
    seanccc17-Peridot
    17-Peridot
    August 1, 2019

    @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'

     

    P
    PaiChung22-Sapphire I
    22-Sapphire I
    August 1, 2019

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

    I
    iguerra16-PearlAuthorAnswer
    16-Pearl
    August 2, 2019

    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!

     

    C
    CarlesColl1-Visitor
    1-Visitor
    August 5, 2019

    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

    Terms & ConditionsAccessibility statement