Syntax error with IF EXISTS UPDATE ELSE INSERT

Question

I am using MySQL 5.1 hosted by my provider. This is my request.

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

Mistake

 ... 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 includes

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

but I reduced it to simplify the solution to the problem.

In my searches on this subject, I see links to "ON DUPLICATE KEY UPDATE", but I don’t know what to do about it.

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

Answer 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' 

title_1 is title_1 be a unique column (enforced by the database) in your table.

How insert... on duplicate works, it tries to insert a new row first, but if the insertion is rejected because the key stops it, it allows you to update certain fields.

Answer 3

The syntax for your request is incorrect. Checkout http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

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