0

I want to perform a MySQL upsert operation.

I have a staging table:

create table if not exists `stg_user`(
  `id` bigint(20) default NULL,
  `name` varchar(255) default NULL,
  `charter` varchar(255) default NULL,
  `school` varchar(255) default NULL
  ) DEFAULT CHARSET=utf8;
  
  INSERT INTO `stg_user` 
  (`id`, `name`, `charter`, `school`) 
  VALUES
  (1001, 'Alex', 'on', 'test hs'),
  (1005, 'Bob', NULL, 'test hs')
  ;

I have a final user table with the same structure

create table if not exists `user`(
  `id` bigint(20) default NULL,
  `name` varchar(255) default NULL,
  `charter` varchar(255) default NULL,
  `school` varchar(255) default NULL
  ) DEFAULT CHARSET=utf8;

Now, I want to insert and update records in my user table based on the id field from the stg_user table.

For example, every time I have new data loaded in the stg_user table, I would compare the records in the user table. If records exist for the id field, it will update the rest of the fields, else it will insert as a new record in the user table.

I have come up with the below query:

insert ignore into user (`id`, `name`, `charter`, `school`) 
SELECT DISTINCT
s.id, s.name, s.charter, s.school 
FROM stg_user AS s 
LEFT JOIN user AS u
    ON u.id = s.id
WHERE u.id IS NULL;

But it's inserting the updated record instead of updating if I try to add a new record in the stg_user table.

Is there something I am missing or doing wrong?

Barmar
  • 741,623
  • 53
  • 500
  • 612
aki2all
  • 429
  • 1
  • 8
  • 24
  • 1
    You need a unique index for `INSERT IGNORE` to detect duplicates. – Barmar Oct 28 '21 at 17:43
  • 3
    Upsert is `INSERT ... ON DUPLICATE KEY UPDATE ...`, not `INSERT IGNORE`. – Barmar Oct 28 '21 at 17:44
  • yes, you need a unique index on field id and change the **INSERT** to **INSERT INTO ..... ON DUPLICATE KEY UPDATE ......** if you want to update duplicate rows see: https://mariadb.com/kb/en/insert-on-duplicate-key-update/ – Bernd Buffen Oct 28 '21 at 17:46
  • INSERT IGNORE will ignore the update phase... ON DUPLICATE KEY UPDATE will – Lelio Faieta Oct 28 '21 at 17:47

0 Answers0