Am facing this error when am trying to update column data in SQL dynamically please can anyone give me a solution how to update with correct syntax using with expressions or without
You would want to enable the expression (click the = next to the “SQL Statement” input) and then set the input field to:
"Update rakesh_sf_customer_order" +
" SET Error_reason = " + $Error_reason +
" Where Customer_ID = " + $Customer_ID + ";"
2 Likes
Agreed with @ddellsperger
@rjapala: At any point you want to use the fields of input/output schema in your “execute” snaps, you’ll have to select the “=” decorator.
And when you do that, your query which is a string needs to be put under " " quotes.
Thank you so much for your reply @ddellsperger and @darshthakkar
But i am facing same issue again after i toggle the = expression
you’ll probably have to put single quotes around the string for $Error_reason
"Update rakesh_sf_customer_order " +
"SET Error_reason = '" + $Error_reason + "' "
"Where Customer_ID = " + $Customer_ID + ";"
1 Like
@rjapala: If you’re copy pasting the expression shared by @ddellsperger, you might see an error again.
Please put the double quotes by yourself as copy pasting the quotes throws an error on the snaps.
Even after modifying your query, you see an error, can you please confirm whether the $Error_reason and $Customer_ID is a valid field? We may be overlooking the typos if you have named it differently.
Best Regards,
Darsh
1 Like
Thank you for your reply @ddellsperger i tried giving quotes as well for string but facing same issue again
@darshthakkar yes the both fields and datatypes are valid but also the same issue am facing
@ddellsperger @darshthakkar one thing when i just validate the pipeline it is not throwing an error when i execute then it is throwing an error
This is really odd @rjapala.
Can you please try the following:
- Enable the error view on MySQL Execute and try running the pipeline again.
- Remove WHERE statement from your query however have a filter snap before My-SQL Execute so that it updates only 1 test record of your choice, please try with this and let us know your findings.
Thank you for suggestion @darshthakkar I have tried with different approach using Pipeline execute snap and i have kept the My sql execute snap in child pipeline it is working successfully now
Thanks for the support @ddellsperger @darshthakkar
1 Like
Here’s an expression that I’ve been using to shorthand the string concatenation in generating an sql statement
"update my_table set my_field = '\$my_update_field', my_null_field = '\$my_null_field' where my_field = '\$my_condition_field'".replace(/\$\w+/gi, m=> eval(m) ).replace(/'null'/gi, 'null')
1 Like
That’s good news @rjapala, congratulations.
I’m still amused on why your previous query didn’t work, I will try it out on my end when I find a use case and share the findings here.
@rjapala - if you have your pipeline in Dev, you may want to try @alchemiz’s solution, I’m sure that will work 100%.
One thing i faced with all the tries is it is showing common error “Batch operation failed” i still dont know why this error is throwing,
So i have used Pipeline execute snap and updating the record in child pipeline then it is working fine.
Iam thinking the problem is with Batch operation Previously i tried with single record it worked fine.
You guys know why this error throwing Or the Mysql execute snap does not support batch operation iam confused
In your query, whatever string values you have pls quote them. If Set has string value then say Error_reason=‘$Error_reason’. And if where has string value then do WHERE Customer_ID=‘$Customer_ID’.
1 Like
Share ur runtime id
Your missing the replace function
"UPDATE rakesh_sf_customer_order SET Error_reason = '\$Error_reason' WHERE Customer_ID = '\$Customer_ID'".replace(/\$\w+/gi, m=> eval(m) ).replace(/‘null’/gi, ‘null’)
The closing ";" is what causing the batch operation failed
Thank you so much @alchemiz
I removed semicolon ; in the my sql execute snap then the query is working fine now.
@darshthakkar Thank you for the support, Now query is working fine
2 Likes