Insert into table if not exists else update

Question

I'm trying to create a script so that it checks whether there is a duplicate of, say for example a type of car. It would check the make and type of car (volvo 260, volvo) from the database, then if it exists the user is allowed to change the speed etc. of the car. I've got this so far:

INSERT INTO cars (id, car_id, car_name, car_make car_speed, car_passengers)
VALUES (1, 1, 'volvo', 'S60', 150, 4)
ON DUPLICATE KEY UPDATE car_speed=VALUES(speed);

I would also like it to check the car name as well as the car make. Is that possible to do?

The logic of what should happen when a duplicate insert occurs is not clear to me. — Tim Biegeleisen, Feb 16 '18 at 07:59
You have to use **Google** thing, I heard that's useful for problems like theses ! — Shady Boshra, Feb 16 '18 at 08:05

score 0 · Answer 1 · edited Feb 16 '18 at 08:13

0

You have to make a key with unique car_make and car_name with that statement

ALTER TABLE `db_name`.`table_name` ADD UNIQUE `key_name` (`car_name`, `car_make`);

edited Feb 16 '18 at 08:13

René Hoffmann

2,647
2
18
37

answered Feb 16 '18 at 08:06

Satish Gupta

300
1
10

That would just fulfill the requirement to get `on duplicate key` in action. The insert statement would still not work. – René Hoffmann Feb 16 '18 at 08:15
thanks, @René Hoffmann for correction – Satish Gupta Feb 16 '18 at 08:17

score 0 · Accepted Answer · answered Feb 16 '18 at 08:12

First of all, you should read the documentation thoroughly.

Then, you will realize that your usage of values() in the on duplicate is completely wrong.

As Satish Gupta said, you will need a unique key on columns car_name and car_make with

ALTER TABLE db_name.table_name ADD UNIQUE key_name (car_name, car_make);

The correct statement for your problem in question would be

INSERT INTO cars (id, car_id, car_name, car_make car_speed, car_passengers)
  VALUES (1, 1, 'volvo', 'S60', 150, 4)
  ON DUPLICATE KEY UPDATE car_speed = 150;

then.

Insert into table if not exists else update

2 Answers2