Question 20770 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Upsert with Insert Only Column Value

In MySQL, is it possible to do an upsert but only set a column value on insert (and not set the column value on update).

For example, for a createdBy column, we only want to set the value on insert, we don’t want to override that value on update (because we lose who originally inserted the column).

Note that we only know the currently logged in user. So updatedBy is simple — always use the value of the logged in user. But createdBy is hard. Use the value of the logged in user but only for an insert — don’t override this on update.

Example schema:

CREATE TABLE `movie` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` NVARCHAR(100) NOT NULL,
    `createdBy` NVARCHAR(100) NOT NULL,
    `updatedBy` NVARCHAR(100) NOT NULL,
    UNIQUE INDEX (`name`)
);

Example of a standard upsert:

INSERT INTO `movie` (`name`, `createdBy`, `updatedBy`)
  VALUES ('The Matrix', 'Jill', 'Jill') 
  ON DUPLICATE KEY UPDATE
  `id` = LAST_INSERT_ID(`id`),
  `name` = VALUES(`name`),
  `createdBy` = VALUES(`createdBy`),
  `updatedBy` = VALUES(`updatedBy`)
;

Here’s my attempt to only set the createdBy column on insert using IFNULL. But this doesn’t work and results in createdBy always being null.

INSERT INTO `movie` (`name`, `createdBy`, `updatedBy`) 
  VALUES ('The Matrix', IFNULL(`createdBy`, 'Jill'), 'Jill') 
  ON DUPLICATE KEY UPDATE 
  `id` = LAST_INSERT_ID(`id`),
  `name` = VALUES(`name`),
  `createdBy` = VALUES(`createdBy`),
  `updatedBy` = VALUES(`updatedBy`)
;

Results wanted:

Case 1: Jill runs an upsert that inserts a row.

id = 1
name = 'The Matrix'
createdBy = 'Jill' // Created by Jill
updatedBy = 'Jill' // Last updated by Jill

Case 2: Bob runs an upsert that updates the same row.

id = 1
name = 'The Matrix Reloaded'
createdBy = 'Jill' // Created by Jill (do not change value on update)
updatedBy = 'Bob' // Last updated by Bob
Tags:

2

Answers


  1. 0

    Try this:

    INSERT INTO `movie` (`name`, `createdBy`)
  VALUES ('The Matrix', 'Jill')
  ON DUPLICATE KEY UPDATE `name` = VALUES(`name`)
;
    Login or Signup to reply.
  2. 0

    I created a fiddle guessing that Name is the Key, feel free to give it a try here.

    This is the basic syntax:

    INSERT INTO `movie` (`name`, `UpdatedBy`,`CreatedBy`)
  VALUES ('Star wars', 'NameA','NameB')
  ON DUPLICATE KEY UPDATE `UpdatedBy` = VALUES(`UpdatedBy`)
;

    Notice: NameA and NameB can be the same so you dont get nulls on inserts

    Hope it helps 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search