insertion sur clé dupliquée lot de mise à jour avec colonnes de mise à jour séparées

Question

J'ai une situation où j'ai un tableau (col1 (pk), col2, col3, col4) et un ensemble d'enregistrements que je dois insérer dans une table et mettre à jour en cas de duplication de la clé. J'aimerais effectuer une requête par lot pour accélérer les choses. Cependant, col4 n'a pas de NOT NULL contrainte. Le problème se pose lorsque je veux mettre à jour avec des enregistrements (val1, val2, val3, None), (val4, val5, val6, val7) . Pour le premier enregistrement, je ne veux pas que la colonne 4 soit mise à jour (Si (val1, val2, val3, val8) a existé dans DB, je ne voudrais pas remplacer val8 parce que None signifierait l'absence de valeur, contrairement à la définition explicite de Null). Cependant, pour le deuxième enregistrement, je voudrais mettre à jour col4 car une valeur explicite est transmise. Cela conviendrait parfaitement à un enregistrement pour lequel je définirais simplement les colonnes de mise à jour comme suit col2, col3, et non col4 Mais je veux mettre cette requête en lot et j'ai besoin de disposer des éléments suivants col4 mettre à jour lorsqu'une valeur lui est transmise et ne pas mettre à jour lorsque je n'ai pas de valeur. J'aurais logiquement besoin de quelque chose comme ce qui suit.

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

Il est clair que cela peut être fait en faisant une série d'instructions séparées, mais j'aimerais trouver un moyen de le faire par lots. Y a-t-il un moyen de faire cela, ou une autre méthode, en SQL ?

Answer 1

Le nombre de champs dans VALUES doit être le même que dans INSERT. Mais vous pouvez simplement passer NULL pour les col4 et utiliser COALESCE dans la partie UPDATE.

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)

Answer 2

Est-ce que c'est ce que vous recherchez ?

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)
;

Answer 3

Dans le cadre de la on duplicate key update de l'insertion, vous pouvez faire référence aux valeurs insérées à l'aide de la fonction values . Vous pouvez utiliser coalesce pour préserver la valeur avant la mise à jour en cas de 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)

Exemple sur SQL Fiddle.

En réponse à votre commentaire, vous pouvez définir null explicitement avec un case :

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

Le risque évident est que vous ne puissiez plus mettre à jour vers None :)