Help with UpdateRecord or QueryRecord

[#1]

Hi to all,

hope someone can point me on the right direction.

I have something like that:

[ {
    "reference" : "PB",
    "date" : "20230613",
    "barcode" : "C337287V28490010",
    "shipment_number" : ""
}, {
    "reference" : "PB",
    "date" : "20230613",
    "barcode" : "C337287V28490011",
    "shipment_number" : ""
}, {
    "reference" : "PB",
    "date" : "20230613",
    "barcode" : "C337287V28490012",
    "shipment_number" : ""
} ]

I need to update only a specific record with a met condition.

Speaking on a SQL language it should be something like that:

UPDATE FLOWFILE SET shipment_number = '${Shipment_Number_Attribute} ' WHERE barcode = '${Barcode_Attribute}'

Unfortunately this statement in QueryRecord doesn't work, maybe UPDATE not supported?

Moreover is there any way to achieve the same with UpdateRecord?

Many thanks for any help.

Appreciate.

[#2 · Accepted solution]

You either have to select each column one by one to have the same column name you are trying to update in the case statement, or select * but create different column for the case statement and then use jolt transformation to write the value back to the original column.

[#3]

OK , i've found the problem.

My query was:

select *,
case when F3BARC_BarcodeSegnacollo = '${barcode}' then '${Shipment_Number}'
else F3SPNU_NumSpedizione end as F3SPNU_NumSpedizione
from flowfile

But fields selection won't have field in else statement like this:

select F3BARC_BarcodeSegnacollo,
case when F3BARC_BarcodeSegnacollo = '${barcode}' then '${Shipment_Number}'
else F3SPNU_NumSpedizione end as F3SPNU_NumSpedizione
from flowfile

Is there any way to make a select *? I have lots of fields to select.

Many Thanks

[#4]

Moreover,

how to assign multiple values when the case matches?

EX:

select *,
case when F3BARC_BarcodeSegnacollo = '${barcode}' then '${Shipment_Number},${Shipment_Date},${Document_Date}'
else 
F3SPNU_NumSpedizione end as F3SPNU_NumSpedizione
F3SPNU_DataSpedizione end as F3SPNU_DataSpedizione
F3SPNU_NumDocumento end as F3SPNU_NumDocumento
from flowfile

Many Thanks!

[#5 · Accepted solution]

You either have to select each column one by one to have the same column name you are trying to update in the case statement, or select * but create different column for the case statement and then use jolt transformation to write the value back to the original column.

[#6 · Accepted solution]

You have to create a separate case statement for each column you are trying to update similar to what  is done for the shipment number.