MySQL statement for insert if does not exist else update

Question

I am trying to insert multiple rows at the same time and check if they are exist. I want to update if they are exist, otherwise insert.

My primary key is "ID". I don't want to use unique index for "query" column.

What I want to insert/update :

INSERT INTO my_table (query, keyword) VALUES ('home', 'home-link');
INSERT INTO my_table (query, keyword) VALUES ('contact', 'contact-link');

my table:

--------------------------------
ID |  query   |  keyword       |
--------------------------------
1  |  home    |  home-link     |
--------------------------------
2  |  contact |  contact-link  |
--------------------------------

I have tried that but it gives me SQL syntax error #1064:

IF EXISTS(SELECT query FROM my_table WHERE query='home')
THEN
UPDATE my_table SET query='home' AND keyword='home-link' WHERE query='home'
ELSE
INSERT INTO my_table (query, keyword) VALUES ('home', 'home-link')
END IF


IF EXISTS(SELECT query FROM my_table WHERE query='contact')
THEN
UPDATE my_table SET query='contact' AND keyword='contact-link' WHERE query='contact'
ELSE
INSERT INTO my_table (query, keyword) VALUES ('contact', 'contact-link')
END IF

Why is it not possible to add a primary key or at least a unique key, so that you can use insert ... on duplicate key update ...? — Shadow
– Shadow, Commented Jan 29, 2016 at 14:00

Dejavu · Accepted Answer · 2016-01-29 15:25:59Z

2

I have decided to delete the record and then insert the new one

DELETE FROM my_table WHERE query = 'home';
INSERT INTO my_table (query, keyword) VALUES ('home', 'home-link');

answered Jan 29, 2016 at 15:25

Dejavu

7113 gold badges9 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ike Walker · Accepted Answer · 2016-01-29 14:10:38Z

0

Assuming you have a unique index on the query column in your table, you can do an upsert like this:

insert into my_table (query, keyword) values ('home', 'home-link')
on duplicate key update 
keyword = values(keyword)

More info: http://mechanics.flite.com/blog/2013/09/30/how-to-do-an-upsert-in-mysql/

answered Jan 29, 2016 at 14:10

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

7 Comments

Dejavu Over a year ago

Unfortunately I have primary key index for ID (auto increment)

Ike Walker Over a year ago

You can still add a unique index on the query column, assuming you want to enforce uniqueness on that column.

Dejavu Over a year ago

Certainly I want to make the query unique and update if it exists but since I am going to use it for opencart, I don't want to mess up the table. Is it possible to do it without adding unique index?

Shadow Over a year ago

@Dejavu no, on duplicate key update works with primary key or unique index only.

Dejavu Over a year ago

What about with IF ELSE statement ?

|

Ike Walker · Accepted Answer · 2016-01-29 14:46:27Z

0

If you don't add a unique index to the query column, then you can't do an upsert using only SQL. The conditional syntax you tried is not supported.

You could write a stored procedure to do this, or use a scripting language (bash, python, etc).

Another option is to install common_schema and use the conditional logic in QueryScript. Here's an example using common_schema QueryScript:

call common_schema.run("
  if (
  select count(*)=0 
  from test.my_table 
  where query='home'
  )
  {
    insert into test.my_table (query, keyword) values ('home', 'home-link');
  }
  else
  {
    update test.my_table set keyword='home-link' where query='home';
  }
");

answered Jan 29, 2016 at 14:46

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

1 Comment

Dejavu Over a year ago

Ok now I see how it works. I think first I'll delete the row then insert the new one :)

Miron Foerster · Accepted Answer · 2023-01-14 12:59:42Z

0

Conceptually there is no problem with the approach of using an if-else statement for this. The error is caused by the if-else syntax used in the provided SQL.

The correct if-else syntax would be (Note the different use of keywords):

IF (Expression )
BEGIN
  -- If the condition is TRUE then execute the following statement
  True Statements;
END
 
ELSE
BEGIN
  -- If the condition is False then execute the following statement
  False Statements;
END

Code from: https://www.sqlshack.com/sql-if-statement-introduction-and-overview/

answered Jan 14, 2023 at 12:59

Miron Foerster

215 bronze badges

Collectives™ on Stack Overflow

MySQL statement for insert if does not exist else update

4 Answers 4

Comments

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related