Database Insert using Write to Data Store Entity Smart Service

Question

Hi All, 
 I have had good success using the Write to Data Store Entity Smart Service to INSERT rows into database tables using CDTs. I thought there will be an Update Database Smart Service just like there is a Delete from Data Store Entity but I read on Appian documentation that the Write to Data Entity is the same smart service used to UPDATE database rows. I also tried to find examples without success. 
 The Write to Data Store Entity accepts a Data Store Entity node input but I am having a hard time finding the parameter where I can specify the id for the primary key of the table and also I cannot see the parameter where I can specify which column in the database I want to update, and lastly the new value. 
 As an example, my CDT looks like this: 
 id, department, type, currentStatus 
 For the above CDT, I am trying to set the value of currentStatus to "Initiator Rework" for the row with id coming from pv!requestData.requestId. How can I configure the Write To Data Store Entity to achieve this? 
 Thanks

rajats246 · Answer

Hi , please check whether you have specified id as primary key . While updating you have to pass all the parameters specified in CDT. If you are not passing them it will make it null. While updating you have to pass primary key. Where as on insert you need not to pass primary key

Sowrabha Rajashekar · Answer

Hi Susane, 
 To specify Primary Key of the CDT: 
 Open "my CDT" you have created above, and in the fields, you can find columns like Name, Type, Length, Array, Key and some others. Click on the Key column for the field for which you want to set primary key. Say "id" in the "my CDT". In the dialog box that opens up, click on "Primary Key" as Key Constraint and check the check box below to make it Auto generated. Save your changes. 
 But then it should not be a problem, if you are creating a CDT, through create CDT from Database Table or View option, for Appian pre-selects the field which is marked as primary key in the table to be the primary key in Appian as well. Which version of Appian are you using? 
 "also I cannot see the parameter where I can specify which column in the database I want to update, and lastly the new value." 
 Unlike Database, you need not mention which field needs to be updated. You just pass the entire cdt with new values and Appian does the update for you. 
 For Ex, 
 So for the first time you write to database with values like below, 
 pv!myCDT.id:null, 
 pv!myCDT.department:Finance, 
 pv!myCDT.type:LoanRequest, 
 pv!myCDT.currentStatus: Requested 
 and in the corresponding DB table, it is stored as below. 
 Id Department Type Current Status 
 1 Finance LoanRequest Requested 
 Now when you want to update, you send in the id number, (unlike null when you insert), so that Appian knows which rows to update in the DB. Also, unlike an update query in Database, you need to send all values for the update of a particular row, both changed and unchanged, otherwise other fields will be nullified. 
 pv!myCDT.id:1, 
 pv!myCDT.department:Finance, 
 pv!myCDT.type:LoanRequest, 
 pv!myCDT.currentStatus: InitiatorRework 
 And in the DB 
 Id Department Type Current Status 
 1 Finance LoanRequest InitiatorRework 
 Sending only id and the field to be updated, results in loss of data as below 
 Id Department Type Current Status 
 1 null null Initiator Rework 
 And what is the type of pv!requestData? The myCDT isn't having a requestId field?

jamesp0015 · Answer

Hi susana197 , 
 As per my understanding by your requirement, you need to update the "currentStatus", by passing only "id" value. 
try to use "Query Database" smart service or else execute stored procedure. 
if you want to update by using Write to datastore Entity, then you must pass all the other values like "type" and "department". 
 
Thanks.