CODEWITHSUNDEEP
mysqlsqlinsertquery-optimizationbatch-query
curiousgeorge
curiousgeorge

Reputation: 621

insert on duplicate key update batch with separate update columns

I have a situation where I have a table (col1 (pk), col2, col3, col4) and a set of records which I need to insert into a table and on duplicate key update them. I would like to do a batch query to speed things up. However, col4 doesn't have a NOT NULL constraint. The problem arises when I want to update with records (val1, val2, val3, None), (val4, val5, val6, val7). For the first record, I don't want column 4 to be updated (If (val1, val2, val3, val8) existed in DB I wouldn't want to override val8 because None would signify lack of value as opposed to explicit setting to Null). However, for the second record, I would want to update col4 because an explicit value is passed. This would be fine with one record where I would just set the update columns to be col2, col3, and not col4, but I want to batch this query and would need to have col4update when a value is passed for it and not update when I don't have a value. I would logically need something like given below. 

INSERT INTO table1
  (col1, col2, col3, col4)
VALUES
  ('val1', 'val2', 'val3'), ON DUP KEY UPDATE col2, col3
  ('val5', 'val6', 'val7', 'val8'), ON DUP KEY UPDATE col2, col3, col4
  ('val9', 'val10', 'val11') ON DUP KEY UPDATE col2, col3

Clearly this can be done by just making it a series of separate statements, but I would like to find a way to batch this. Is there any way this, or a different method, can be done in sql?

Upvotes: 3

Views: 1926

Answers (3)

Andomar
Andomar

Reputation: 238076

In the on duplicate key update part of the insert, you can refer to the inserted values with values. You can use coalesce to preserve the pre-update value in case of null:

INSERT INTO YourTable (col1, col2, col3, col4) VALUES
    ('val1', 'val2', 'val3', null)
,   ('val5', 'val6', 'val7', 'val8')
,   ('val9', 'val10', 'val11', null)
ON DUPLICATE KEY UPDATE
    col1 = values(col1)
,   col2 = values(col2)
,   col3 = values(col3)
,   col4 = coalesce(values(col4), col4)

Example on SQL Fiddle.

In reply to your comment, you can set null explicitly with a case:

,   col4 = case values(col4) 
           when 'None' then null 
           else coalesce(values(col4), col4)
           end

The obvious risk here is that you can no longer update to None :)

Upvotes: 1

Alexey
Alexey

Reputation: 2478

Is this the thing that you are looking for?

INSERT INTO table1
  (col1, col2, col3, col4)
VALUES
  ('val1', 'val2', 'val3', null)
  ('val5', 'val6', 'val7', 'val8')
  ('val9', 'val10', 'val11', null)
ON DUPLICATE KEY UPDATE
  col2 = values(col2),
  col3 = values(col3),
  col4 = coalesce(values(col4), col4)
;

Upvotes: 2

Paul Spiegel
Paul Spiegel

Reputation: 31792

The number of fields in VALUES must be the same as in INSERT. But you can just pass NULL for the col4 and use COALESCE in the UPDATE part.

INSERT INTO table1
  (col1, col2, col3, col4)
VALUES
  ('val1', 'val2',  'val3',  NULL),
  ('val5', 'val6',  'val7',  'val8'),
  ('val9', 'val10', 'val11', NULL)
ON DUPLICATE KEY UPDATE
  col2 = VALUES(col2),
  col3 = VALUES(col3),
  col4 = COALESCE(VALUES(col4), col4)

Upvotes: 1

Related Questions