CODEWITHSUNDEEP
mysql
Dejavu
Dejavu

Reputation: 713

MySQL statement for insert if does not exist else update

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

Upvotes: 2

Views: 4642

Answers (4)

Miron Foerster
Miron Foerster

Reputation: 21

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/

Upvotes: 0

Dejavu
Dejavu

Reputation: 713

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');

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65527

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';
  }
");

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65527

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/

Upvotes: 0

Related Questions