If new, INSERT. if existing, UPDATE. - MySQL (ON DUPLICATE KEY UPDATE)

If new, INSERT. if existing, UPDATE. - MySQL (ON DUPLICATE KEY UPDATE)

Create test_table and add 2 records.

view plaincopy to clipboardprint?

1. CREATE TABLE IF NOT EXISTS `test_table` (  
2.   `id` int(11) NOT NULL,  
3.   `name` varchar(30) NOT NULL,  
4.   `email` varchar(100) NOT NULL,  
5.   PRIMARY KEY (`id`)  
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
7.   
8.   
9. INSERT INTO `test_table` (`id`, `name`, `email`) VALUES  
10. (1, 'sam1', 'sam1@xx.com'),  
11. (2, 'sameera', 'sameera@xx.com');  

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `test_table` (`id`, `name`, `email`) VALUES
(1, 'sam1', 'sam1@xx.com'),
(2, 'sameera', 'sameera@xx.com');

Insert id '1' again with different data.

view plaincopy to clipboardprint?

1. INSERT INTO test_table(id, name, email)  
2.      VALUES ('1', 'sam1', 'sam1@xx.com')  
3.      ON DUPLICATE KEY UPDATE  
4.      id = values(id),  
5.      name = values(name),  
6.      email= values(email);  

INSERT INTO test_table(id, name, email)
     VALUES ('1', 'sam1', 'sam1@xx.com')
     ON DUPLICATE KEY UPDATE
     id = values(id),
     name = values(name),
     email= values(email);

Record will update