Simple update into a database using the Query Database smart service

Question

Dear all, 
 I would like to make a quick update to just one database column and trying to use the Write to Database smart service, I am required to populate all the other fields. Because I do not need to start getting all the other fields just so I can update on field, I am trying to use the Query Database smart service instead. 
 I have the following simple database update query: 
 UPDATE cshvcashadvancedata SET currentstatus = "Manager Rework -test has worked" WHERE id = ac!requestId 
 In the setup tab, I defined the above query. 
 In the data input tab, I added an ac (see screenshot below). However, the database update is not getting performed. I also noticed that Appian automatically added a new input node - ~1.requestId (I'm not sure what that is for). 
 Is there anything I am missing? 
 Thanks

gianninol · Accepted Answer

Hello, 
 
It is actually possible to update only your single field with the Write to Database smart service. If you create a different CDT that references the same table, but that only has the primary key field and the field you wish to write to, you can use that to update only the single column. I believe that is the simplest solution to your problem.

Dastagiri Dudekula · Answer

The Query Database smart service is used to retrieve information from an external database using a SQL query, and update or create process variables using the query results . 
 To insert or update data, refer to Write to Data Store Entity and [Write to Multiple Data Store Entities

simples533 · Answer

Hi Susana, Don't create manually new Input under Data tab, When you add ac!variable_name in your query under setup tab -> Database Query, Appian automatically create input variable and you need to pass input value in it only. 
 Other thing to keep in mind is Assignment tab, make sure you have selected Run as Whoever design Process. 
 
 Regards, Simple Shah

Farnaz · Answer

Hi Susan, As other suggested, please follow the below steps (1) Use Write to Data Store Entity Smart Service instead of Query Database. 
 (2) To update specific columns, please create a CDT and specify the <xsd:element> for those columns (not all). While creating this CDT, the primary key should not have anotation @GeneratedValue. 
 (3) Use this CDT in the Smart Service and map the columns values to the respective ac!variable. Also, map the primary key value. So the compiler will know that this is an update not an insert. Hope this helps!!! Thanks, Farnaz

Bala · Answer

Populate value for ~1.requestId using pv. 
 "~1." represents query name that you see in setup tab. Your "requestid" node input will not be used by Query Database smart service.

Farnaz · Answer

Hi Susan, 
 
While updating a column in DB, you need the Where Clause. 
 
The same need not be replicated in Appian Concept. 
 
If the pv!currentRequest .id =30, and the rest of the columns hold the appropriate value, this implies that the row where id = 30 should be updated, no need to specify the where clause. 
 
Hope this answers your query!! 
 
Thanks, 
Farnaz