MariaDB connector for NodeJs | How to insert value if not exists

Question

I'm trying to run a batch query from a pool. the query itself is simple but I'm having trouble with the NodeJS Connector

the query with the '?' placeholders is

INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
(SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1

what I'm trying to achieve is to check if a value is present before inserting it. the TS code I'm using is something like this:

const INSERT_REGION =
  'INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
(SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1';

const insertRegionParam = [['ITC1', 'ITA', 'EU', 'ITC1'] , ['ITP1', 'ITA', 'EU', 'ITP1'] ]

  private insertRegions$(insertRegionParam: string[][]) {
    return from(this.pool.batch(INSERT_REGION, insertRegionParam)).pipe(
      catchError((err: mdb.SqlError) => throwError(() => err))
    );
  }

I'm getting this error:

R_DUP_FIELDNAME: Duplicate column name '?'
sql: INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
    (SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1
    at /code/dist/resources/regions-sectors/regions-sectors.controller.js:24:22
    at /code/node_modules/rxjs/dist/cjs/internal/operators/catchError.js:13:51
    at OperatorSubscriber._this._error (/code/node_modules/rxjs/dist/cjs/internal/operators/OperatorSubscriber.js:43:21)
    at Subscriber.error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:60:18)
    at Subscriber._error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:84:30)
    at Subscriber.error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:60:18)
    at Observable.init [as _subscribe] (/code/node_modules/rxjs/dist/cjs/internal/observable/throwError.js:8:58)
    at Observable._trySubscribe (/code/node_modules/rxjs/dist/cjs/internal/Observable.js:41:25)
    at /code/node_modules/rxjs/dist/cjs/internal/Observable.js:35:31
    at Object.errorContext (/code/node_modules/rxjs/dist/cjs/internal/util/errorContext.js:22:9)

like if the string has not been parsed

Answer 1

You have:

FROM (SELECT ?, ?, ?) AS tmp

When you join or select from a subquery, the "table" thus formed (tmp in this case) has to have unique names for each column, even in contexts where you don't use the columns by name. You can simply give arbitrary names:

FROM (SELECT ? x, ? y, ? z) AS tmp

but it's probably better to use names that reflect the values:

FROM (SELECT ? region_code, ? country_code, ? continent_code) AS tmp