mysql insert update 쿼리

mysql insert on duplicate update

 

1.insert 실행시 update가 필요할 경우

2.키값이 필요함

3.insert 실행 후 update 실행됨

 

1. insert update

 

INSERT INTO [table] (col1, col2) values (val1, val2)
	ON DUPLICATE KEY UPDATE col1 = 'val1'

 

2. insert update

 

INSERT INTO [table] (col1, col2, col3) values (val1, val2, col3), (val1, val2, col3)
	ON DUPLICATE KEY UPDATE col3 = values(col1) + values(col2)

 

3.insert update

 

 INSERT INTO [table] (
      col1
     ,col2
     ,col3
     ,col4
  ) VALUES (
      'val1'
     ,'val2'
     ,'val3'
     ,'val4'
  )
   ON DUPLICATE KEY UPDATE
   col1 = 'val1',
   col2 = 'val2',
   col3 = 'val3',
   col4 = 'val4';

 

4.insert update

 

 INSERT INTO [table] (
      col1
     ,col2
     ,col3
     ,col4
  ) VALUES (
      'val1'
     ,'val2'
     ,'val3'
     ,'val4'
  )
   ON DUPLICATE KEY UPDATE
   col4 = values(col1)+values(col2)+values(col3);

남용은 별로인듯

* value(col1) mariaDB > 10.3.3

  values(col1) mariaDB .. 10.3.2