DB Update Statement with In Clause in Mulesoft

 Here we will look at how Update SQL query with IN clause can be constructed when there are multiple values coming as array as IN clause input

Assuming that dateSent must be updated for all the eventIds sent as input using IN clause.

Input:

{

"dateUpdated": "2025-01-14",

"eventId": ["234er-4356","234er-4896", "4dse4-4321"]

}

Building Update SQL in Dataweave

%dw 2.0

output application/json

---

{

"dynamicUpdateStatement": "Update table_name set dateSent='" ++ payload.dateUpdated ++ "' where eventId in (" ++ "'" ++ (payload.eventId joinBy("','") ++ ")")

}

Output:

{

"dynamicUpdateStatement": "Update table_name set dateSent='2025-01-14' where eventId in ('234er-4356','234er-4896','4dse4=4321')"

}

Screenshot:

Another Solution:

Dataweave:

%dw 2.0

output application/json

---

{

"dynamicUpdateStatement": "Update table_name set dateSent='" ++ payload.dateUpdated ++ "' where eventId in (" ++ (payload.eventId map ("'" ++ $ ++ "'") joinBy(",") ++ ")")

}

Store the above dataweave as variable and reference the same in "DB Connection Update " sql text  using #[vars.updateSql.dynamicUpdateStatement]  assuming variable name as updateSql.