MySql Table Insert if not exist otherwise update

Databases
1

Guys how can i insert into table if record doesn’t exist and update otherwise?

CREATE TABLE `testing` (
  `name` varchar(200) DEFAULT NULL,
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `testing`
 ADD id int NOT NULL AUTO_INCREMENT primary key;

INSERT INTO testing (name)
  VALUES (1)
  ON DUPLICATE KEY UPDATE name= name+ 1;

But it just creates new row every time.

2

Correct because it’s going exactly what you told it to do

ALTER TABLE `testing`
 ADD id int NOT NULL AUTO_INCREMENT primary key;
3

Anyway around that. How can I check if exits update else insert

4

So you have a unique requirement on name?

Are you unaware of how auto_increment works? The auto_increment is basically doing this

SELECT MAX(id) INTO maxID FROM testing
INSERT INTO testing (id, name) VALUES (maxID + 1, name)

So you will NEVER have a duplicate.

5

Thanks Dave I know it increases the id by 1 on each new row add.

Would you be able to come up with the code that will let me as I already mentioned above update if exists else insert using primary id auto_increment.

P.S. I am storing userId which alone or by itself is already a unique number. Maybe I don’t need auto_increment case by storing userId I already make sure the row is unique!

That are your thoughts.

6

Then you MUST insert a value into id

INSERT INTO testing (id, name) VALUES (100, 'John Smith')
7

bingo… you don’t need an auto_increment

8

Well you don’t need an auto_increment, but you DO need a key on the table somewhere.

Also how is this meant to work:
ON DUPLICATE KEY UPDATE name= name+ 1;
?

How do you add 1 to “m_hutley”? And what happens when you send m_hutley a third time?

9

Thanks guys I added primary key to prevent duplicates but skipped auto_increment

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.