Syntax error with IF EXISTS UPDATE ELSE INSERT

Question

I'm using MySQL 5.1 hosted at my ISP. This is my query

mysql_query("
IF EXISTS(SELECT * FROM licensing_active WHERE title_1='$title_1') THEN
    BEGIN
        UPDATE licensing_active SET time='$time' WHERE title_1='$title_1')
    END ELSE BEGIN
        INSERT INTO licensing_active(title_1) VALUES('$title_1')
    END   
") or die(mysql_error());

The error is

... check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT * FROM licensing_active WHERE title_1='Title1') THEN ' at line 1

My actual task involves

WHERE title_1='$title_1' AND title_2='$title_2' AND version='$version' ...ETC...

but I have reduced it down to make things simpler for my problem solving

In my searches on this, I keep seeing references to 'ON DUPLICATE KEY UPDATE', but don't know what to do with that.

BenRoob · Accepted Answer · 2018-11-07 11:41:00Z

24

Here is a simple and easy solution, try it.

$result = mysql_query("SELECT * FROM licensing_active WHERE title_1 ='$title_1' ");

if( mysql_num_rows($result) > 0) {
    mysql_query("UPDATE licensing_active SET time = '$time' WHERE title_1 = '$title_1' ");
}
else
{
    mysql_query("INSERT INTO licensing_active (title_1) VALUES ('$title_1') ");
}

Note: Though this question is from 2012, keep in mind that mysql_* functions are no longer available since PHP 7.

edited Nov 7, 2018 at 11:41

BenRoob

1,7225 gold badges22 silver badges26 bronze badges

answered Sep 15, 2012 at 11:54

Sohail Ahmed

1,66714 silver badges23 bronze badges

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

1 Comment

Openstar63 Over a year ago

That works for me, thanks. And seems straightforward to understand and remember. It might not be super-efficient, but this isn't yet a high traffic application so I'm sure this method will be fine for a year or more. Thank you.

Fluffeh · Accepted Answer · 2012-09-15 10:33:27Z

13

This should do the trick for you:

insert into 
    licensing_active (title_1, time) 
    VALUES('$title_1', '$time') 
    on duplicate key 
        update set time='$time'

This is assuming that title_1 is a unique column (enforced by the database) in your table.

The way that insert... on duplicate works is it tries to insert a new row first, but if the insert is rejected because a key stops it, it will allow you to update certain fields instead.

answered Sep 15, 2012 at 10:33

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

5 Comments

Openstar63 Over a year ago

Hi Fluffeh. title_1 is not unique. The only unique column I have is an int named id. It is set as 'auto_increment', 'unique' and 'primary'. I don't access it directly. Am still struggling with the same error.

Openstar63 Over a year ago

Couldn't quite get there, so I did the logic in PHP - as described by Dark Wish. Thanks though.

Raza Ahmed Over a year ago

works for me when I remove set at the end. i.e. INSERT INTO <table> (col1, col2) VALUES('f1','f2') ON DUPLICATE KEY UPDATE col2='fn'

BamsBamx Over a year ago

Just remember to use WHERE clause!!! From here: w3schools.com/sql/sql_update.asp

The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Ry- Over a year ago

@BamsBamx: This is all one INSERT statement.

Robert Longson · Accepted Answer · 2016-09-18 17:33:43Z

1

The syntax of your query is wrong. Checkout http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Use the on duplicate key syntax to achieve the result you want. See http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

edited Sep 18, 2016 at 17:33

Robert Longson

125k27 gold badges272 silver badges258 bronze badges

answered Sep 15, 2012 at 10:38

JvdBerg

21.8k8 gold badges40 silver badges55 bronze badges

Comments

abelarda · Accepted Answer · 2022-03-08 10:17:18Z

0

Another solution

$insertQuery = "INSERT INTO licensing_active (title_1) VALUES ('$title_1')"; 
if(!$link->query($insertQuery)){ // Insert fails, so update
    $updateQuery = "UPDATE licensing_active SET time='$time' WHERE title_1='$title_1'";
    $link->query($updateQuery);
}

answered Mar 8, 2022 at 10:17

abelarda

431 silver badge6 bronze badges

Comments

Tyler2P · Accepted Answer · 2022-05-30 13:42:03Z

0

Here is the example I tried and its works fine:

INSERT INTO user(id, name, address) VALUES(2, "Fadl", "essttt") ON DUPLICATE KEY UPDATE name = "kahn ajab", address = "Address is test"

edited May 30, 2022 at 13:42

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered May 11, 2022 at 5:32

Engr.Aftab Ufaq

1

1 Comment

Tyler2P Over a year ago

Your answer could be improved by adding more information on what the code does and how it helps the OP.

Bryce · Accepted Answer · 2022-03-24 22:01:57Z

-1

I am amazed to see so many useless codes and answers... Just replace INSERT with REPLACE.

¯\(ツ)/¯

answered Mar 24, 2022 at 22:01

Bryce

1

1 Comment

Norman Bird Over a year ago

The answer could have been provided without attitude, which is not required or helpful.

Collectives™ on Stack Overflow

Syntax error with IF EXISTS UPDATE ELSE INSERT

6 Answers 6

1 Comment

5 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

5 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related