Syntax error with IF EXISTS UPDATE ELSE INSERT

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.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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.

Method 3

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

Method 4

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

Method 5

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"

Method 6

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

¯(ツ)/¯

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating