MYSQL on duplicate key update copy values from inserted records

[kir4ik]

didn't find a way to use mysql VALUES on a duplicate insert.

You have to write something like this to make it work.

this.connection
      .insertInto(productTable)
      .values(products)
      .customizeQuery({
        afterQuery: this.connection
          .rawFragment`on duplicate key update product.amazonSalesRank = {VALUES(product.amazonSalesRank), product.brandId = VALUES(product.brandId), product.categoryId = VALUES(product.categoryId), product.isDeleted = VALUES(product.isDeleted), product.isValid = VALUES(product.isValid), product.itemsCount = VALUES(product.itemsCount), product.msrp = VALUES(product.msrp), product.name = VALUES(product.name), product.nrsSales = VALUES(product.nrsSales), product.reviewStatus = VALUES(product.reviewStatus), product.size = VALUES(product.size)}`
      });

if use

.onConflictDoUpdateSet({
        isDeleted: this.connection.fragmentWithType('boolean', 'required').sql`VALUES(${productTable.isDeleted})`
      })

then the mysql will throw an error on such an expression in on duplicate key update

insert into product (...) values(...) on duplicate key update isDeleted = case when VALUES(isDeleted = 1) then 1 else 0 end

I need it to be so, then mysql will understand

insert into product (...) values(...) on duplicate key update product.isDeleted = VALUES(product.isDeleted)

[juanluispaz]

mmm, interesting

Can you share with me the productTable and the connection definition, please, then I can fix the wrong query.

For some reason it is using Oracle or SqlServer code generation on: case when VALUES(isDeleted = 1) then 1 else 0 end

[juanluispaz]

please, include an example of values for the products variable

[kir4ik]

need an easy way to do this => product.amazonSalesRank = VALUES(product.amazonSalesRank)

it would be nice to have something like this

connection.insertInto(productTable).values([{name: 'test'}]).onConflictDoUpdateSet({name: connection.applyValues(productTable.name)})

insert into product (`name`) values('test') on duplicate key update product.name = VALUES(product.name)

example products

class Connection extends MySqlConnection<'Connection'> {
  allowEmptyString = true;
}

const connection = new Connection(new MySqlPoolQueryRunner(mysql.getMySQLDriver(), 'mySql'));

// UNIQUE INDEX `source-sourceId` (`source`, `sourceId`)
class ProductTable extends Table<Connection, 'ProductTable'> {
  id = this.autogeneratedPrimaryKey('id', 'int');
  source = this.columnWithDefaultValue<'AMAZON' | 'CSV'>('source', 'enum', 'enum');
  sourceId = this.column('sourceId', 'string');
  name = this.column('name', 'string');
  brandId = this.optionalColumn('brandId', 'int');
  /**
   * bigint
   */
  categoryId = this.optionalColumn('categoryId', 'string');
  /**
   * bigint
   */
  amazonSalesRank = this.optionalColumn('amazonSalesRank', 'string');
  nrsSales = this.optionalColumn('nrsSales', 'int');
  msrp = this.optionalColumn('msrp', 'double');
  itemsCount = this.optionalColumn('itemsCount', 'int');
  size = this.optionalColumn('size', 'string');
  isDeleted = this.columnWithDefaultValue('isDeleted', 'boolean', new CustomBooleanTypeAdapter(1, 0));
  isValid = this.columnWithDefaultValue('isValid', 'boolean', new CustomBooleanTypeAdapter(1, 0));
  reviewStatus = this.columnWithDefaultValue<'PENDING' | 'APPROVED' | 'DECLINED'>(
    'reviewStatus',
    'enum',
    'enum'
  );

  constructor() {
    super('product');
  }
}

const productTable = new ProductTable();

async function uploadProducts(): Promise<void> {
  await connection
    .insertInto(productTable)
    .values([
      {
        sourceId: 'any string #1',
        source: 'CSV',
        name: 'Test name #1',
        sourceId: 'any string #1',
        amazonSalesRank: '100',
        brandId: 1,
        categoryId: '123456789123456',
        isDeleted: false,
        isValid: true,
        itemsCount: 10,
        msrp: 123.5,
        size: '1 of 2',
        reviewStatus: 'PENDING'
      },
      {
        sourceId: 'any string #2',
        source: 'CSV',
        name: 'Test name #2',
        amazonSalesRank: '2000',
        brandId: 1,
        categoryId: '123456789123456',
        isDeleted: false,
        isValid: true,
        itemsCount: 300,
        msrp: 12.5,
        size: 'some value',
        reviewStatus: 'PENDING'
      }
    ])
    .onConflictDoUpdateSet({
      name: connection.fragmentWithType('string', 'required').sql`VALUES(${productTable.name})`,
      amazonSalesRank: connection.fragmentWithType('string', 'required')
        .sql`VALUES(${productTable.amazonSalesRank})`,
      brandId: connection.fragmentWithType('int', 'required').sql`VALUES(${productTable.brandId})`,
      categoryId: connection.fragmentWithType('string', 'required').sql`VALUES(${productTable.categoryId})`,
      isDeleted: connection.fragmentWithType('boolean', 'required').sql`VALUES(${productTable.isDeleted})`,
      isValid: connection.fragmentWithType('boolean', 'required').sql`VALUES(${productTable.isValid})`,
      itemsCount: connection.fragmentWithType('int', 'required').sql`VALUES(${productTable.itemsCount})`,
      msrp: connection.fragmentWithType('int', 'required').sql`VALUES(${productTable.msrp})`,
      size: connection.fragmentWithType('string', 'required').sql`VALUES(${productTable.size})`,
      source: connection.fragmentWithType<'CSV' | 'AMAZON'>('enum', 'enum', 'required')
        .sql`VALUES(${productTable.source})`
    }).executeInsert();
}

uploadProducts().catch(console.error);

[juanluispaz]

Release ts-sql-query 1.28.0 with support to reference current value and value to insert in an insert on conflict do update.

Fro more details see the documentation

Please, test it and give me feedback if that resolve your requirement.

Note: I believe in the columns isDeleted and isValid you do not need CustomBooleanTypeAdapter because MySql transform booleans automatically to 0 or 1 (it depends on the version of MySQL)

[kir4ik]

Thank you very much. Everything is fine