如果那么在select中

I am trying to make a script faster and I have decided to include some calculations that used to happen in PHP inside SQL. I have to make an if statement which decides whether to use insert or update if it's needed with the code:

我试图使脚本更快,我已经决定包含一些曾经在PHP内部发生的计算。我必须创建一个if语句,决定是否使用插入或更新,如果代码需要它:

IF (EXISTS (SELECT * FROM `edenteva` WHERE `Time`='1340252716' and `Code`='571119'))
THEN IF (EXISTS (SELECT * FROM `map` WHERE `mega`='571119'))
THEN UPDATE `map` SET `edenteva`='571119' WHERE `mega`='571119'
ELSE INSERT INTO `map`(`mega`,`edenteva`) VALUES ('571119','571119');

But, I am getting a syntax error, can you see what is wrong?

但是,我收到语法错误,你能看出什么是错的吗?

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (EXISTS (SELECT * FROM edenteva WHERE Time='1340252716' and Code='57111' at line 1

1064 - 您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在'IF附近使用正确的语法(EXISTS(SELECT * FROM edenteva WHERE Time ='1340252716',Code ='57111'在第1行)

3 个解决方案

#1

If map.mega has a UNIQUE index defined on it (or it's a PRIMARY KEY) we can use ON DUPLICATE KEY UPDATE thus:

如果map.mega上定义了UNIQUE索引(或者它是PRIMARY KEY),我们可以使用ON DUPLICATE KEY UPDATE:

INSERT INTO TABLE map (mega, edenteva)
VALUES ('571119','571119')
ON DUPLICATE KEY UPDATE edenteva = '571119';

If we only want to do the update or insert when there's a row in another table, we can use the other table as the source for the values:

如果我们只想在另一个表中有一行时进行更新或插入,我们可以使用另一个表作为值的来源:

INSERT INTO map (mega, edenteva)
  SELECT code, code
  FROM edenteva
  WHERE time = '1340252716' AND code = '571119'
ON DUPLICATE KEY UPDATE edenteva = VALUES(edenteva) ;

Good ole SQL Fiddle: http://sqlfiddle.com/#!2/9bb19/1

Good ole SQL Fiddle:http://sqlfiddle.com/#!2/9bb19/1

#2

Well, you give the answer in your question: check the manual ...

那么,你在问题中给出答案:检查手册......

IF statement: http://dev.mysql.com/doc/refman/5.5/en/if-statement.html

IF声明:http://dev.mysql.com/doc/refman/5.5/en/if-statement.html

EXISTS statement: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

EXISTS声明:http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

Hint: you are confusing regular condition expression IF THEN ELSE with the IF statement within a SQL query, which is different.

提示:如果在SQL查询中使用IF语句,那么您会混淆常规条件表达式,这是不同的。

#3

IF THEN ELSE is for stored programs in mysql land...

如果那么ELSE用于mysql域中的存储程序...

so can only be used like this :( so no good news for you or other people expecting more...

所以只能这样使用:(所以没有好消息为你或其他人期待更多......

DELIMITER //

CREATE FUNCTION SimpleCompare(n INT, m INT)
  RETURNS VARCHAR(20)

  BEGIN
    DECLARE s VARCHAR(20);

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m);

    RETURN s;
  END //

DELIMITER ;

#1