Syntax error with IF EXISTS UPDATE ELSE INSERT

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

我在我的ISP上使用MySQL 5.1。这是我的疑问

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.

在我对此的搜索中,我一直看到“ON DUPLICATE KEY UPDATE”的引用,但不知道如何处理它。

3 个解决方案

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

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

这假设title_1是表中的唯一列(由数据库强制执行)。

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.

插入...在重复工作的方式是它首先尝试插入一个新行,但如果插入被拒绝,因为一个键停止它,它将允许您更新某些字段。

#3

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

查询的语法错误。结帐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

使用on duplicate key语法来获得所需的结果。请参阅http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

#1