Update multiple records via SQLselect and one API call

[Dick Honing]

Hi there,

I'm trying to update multiple records via one API Call and was wondering what the best approach is.

I've already created an API in which I sent the SQL as input parameter, i.e.:

{
"SQL": "REPLACE INTO trips (id, name) VALUES (998, 123);REPLACE INTO trips (id, name) VALUES (999, 456);"
}

This works! ... however, only with numeric data. As soon as I want to send string data and ad the '' single quotes, I get the following error:

"SQL Syntax Error near 'test'');REPLACE INTO trips (id, name) VALUES (999, 456)' at line 1",

I've tried escaping the '' quotes, but that doesn't fix the problem.

So I was wondering whether there's another/better approach for this.

Thanks in advance and best regards - Dick

[Wassim]

@Dick-Honing said in Update multiple records via SQLselect and one API call:

This works! ... however, only with numeric

Hi Dick,

First of all what are you using to do this update? (maybe post a screenshot it would be easier)

Second question, why do you use REPLACE INTO instead of

UPDATE trips SET name='yourname' WHERE id=yourid;

?

[Dick Honing]

Hi Wassim

I use Raw SQL Select. I mentioned SQLselect in the subject of my message, but that apparently wasn't clear enough ... sorry.

I use replace instead of update as I want the same function to both create new records as well as modify/update existing records.

Is there a (preferred) method of adding/updating multiple records simultaneously in one API call? ... in other words not having to do an API call for each record your want to add/update ...

Thanks again and best regards - Dick

[Wassim]

@Dick-Honing said in Update multiple records via SQLselect and one API call:

REPLACE INTO trips (id, name) VALUES (998, 123);REP

Can you send a screenshot when you set text values?

[Dick Honing]

@Wassim

[Dick Honing]

@Wassim Hi Wassim, this is probably better SQL code, but still throws back an error when using single quotes '' to enclose strings. Strange enough, this setup works fine if I only use numbers.

[Dick Honing]

Eureka ... I've got it working ... a string needs to be enclosed by escaped double quotes ... so I can now insert/update multiple records in one call

'

[Wassim]

Hello,

Sorry for late answer.

Glad you found a solution!
Well the problem is that we already escape the strings and didn't expect to use it this way with multiple values. @PARAM_parameter is originally meant to be a single value instead of a query.

[Dick Honing]

@Wassim Am I allowed to fill the @PARAM_paramater with multiple parameters or am I pushing the envelope?