CODEWITHSUNDEEP
sqloracle-databasetalend
54l3d
54l3d

Reputation: 3973

Talend-Oracle: Increment field of selected one row

I have a talend job that import files and log into Oracle database, the log table schema is like this (there is no id):

 filename | staus  | try_number
 -----------------------------
 f1       |imorted |    1 
 f2       |detected|    0
 f3       |detected|    3

Initially, all files have status "detected", so they not imported yet and import try number is 0, i want to increment the try number for every import try, the obvious solution is to get the current "try_nmber" by select statement increment it and update table with the new one, but the query will be very ugly:

update mytable set
try_number = (select try_number from mytable where
              field1= value1 and
              field2= value2 and
              filed3= value3)
              +1
where         field1= value1 and
              field2= value2 and
              filed3= value3

I cant find any smart solution via Talend or Oracle to increment a field of a selected row without retrieving the current value, any ideas ?

Upvotes: 1

Views: 151

Answers (1)

UltraCommit
UltraCommit

Reputation: 2276

Try this:

UPDATE   MYTABLE
   SET   TRY_NUMBER = NVL (TRY_NUMBER, 0) + 1
 WHERE   FIELD1 = VALUE1 AND FIELD2 = VALUE2 AND FILED3 = VALUE3;

NVL is used to handle the case in which TRY_NUMBER is NULL.

Since NULL + 1 = NULL in Oracle, we have to transform NULL in an entity that you can sum with 1 returning 1.

NVL transforms TRY_NUMBER in 0 in case when TRY_NUMBER is NULL.

Upvotes: 1

Related Questions