在MySQL中使用IF语句在没有存储过程的情况下进行更新或插入? [英] Using IF statement in MySQL to update or insert without Stored Procedure?

问题描述

是否可以在MySQL中使用IF语句将记录更新或插入到表中，而不必在存储过程中进行操作?

Is it somehow possible to use an IF statement in MySQL to either update or insert a record into a table, without having to do it within a stored procedure ?

鉴于设置的性质，我目前无法使用存储过程，这就是为什么我问..

Given the nature of my setup, I cannot use Stored Procedures at this time, which is why I ask ..

基本上，这就是我想要做的:

Basically this is what I'm trying to do:

IF NOT EXISTS (SELECT ........)
THEN
    INSERT ......
ELSE
    UPDATE ......
END IF

这是我的代码:

IF NOT EXISTS
(
    SELECT * FROM `oc_product_attribute`
    WHERE PRODUCT_ID = (SELECT PRODUCT_ID FROM `oc_product_description` WHERE NAME = 'PRODUCT_NAME_HERE')
    AND ATTRIBUTE_ID = (SELECT ATTRIBUTE_ID FROM `oc_attribute_description` WHERE NAME='ATTRIBUTE_NAME_HERE')
)
THEN
    INSERT INTO `oc_product_attribute`
    VALUES
    (
    SELECT PRODUCT_ID FROM `oc_product_description` WHERE NAME = 'PRODUCT_NAME_HERE',
    SELECT ATTRIBUTE_ID FROM `oc_attribute_description` WHERE NAME='ATTRIBUTE_NAME_HERE',
    1,
    'XYZ'
    )
ELSE
    UPDATE `oc_product_attribute`
    SET TEXT = 'ABC'
    WHERE PRODUCT_ID = (SELECT PRODUCT_ID FROM `oc_product_description` WHERE NAME = 'PRODUCT_NAME_HERE')
    AND ATTRIBUTE_ID = (SELECT ATTRIBUTE_ID FROM `oc_attribute_description` WHERE NAME='ATTRIBUTE_NAME_HERE')
END IF

我收到上述错误:

#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 NOT EXISTS ( SELECT * FROM `oc_product_attribute` WHERE PRODUCT_ID = (SELE' at line 1

请注意，我需要在一个完整的语句中执行以上操作.我无法跨越多个语句来实现上述功能..也没有存储过程:)

Note that I need to do the above in exactly ONE statement. I cannot span to multiple statements to achieve the above functionality .. Also no Stored Procedures :)

已解决:

Solved:

正如戈登所指出的，ON DUPLICATE KEY是我所需要的.这是有效的最终声明:

As pointed out by Gordon, ON DUPLICATE KEY is what I needed. Here is the final statement which works:

INSERT INTO `oc_product_attribute`
VALUES
(
(SELECT PRODUCT_ID FROM `oc_product_description` WHERE NAME = 'PRODUCT_NAME_HERE'),
(SELECT ATTRIBUTE_ID FROM `oc_attribute_description` WHERE NAME='ATTRIBUTE_NAME_HERE'),
1,
'XYZ'
)
ON DUPLICATE KEY UPDATE TEXT='ABC';

但这是假设该表具有PRIMARY KEY或UNIQUE约束，或两者都有.

But this assumes that the table has either PRIMARY KEY or UNIQUE constraint on it, or both.

推荐答案

MySQL在这种情况下具有特殊的功能.它称为INSERT...ON DUPLICATE KEY UPDATE.它所做的是尝试INSERT记录，但是如果它已经存在，它将只是UPDATE. '

MySQL has special functionality on this situation. It's called INSERT...ON DUPLICATE KEY UPDATE. What it does is it tries to INSERT a record but if it already exists, it will just UPDATE it. '

为了使此功能起作用，您需要在表上具有UNIQUE约束.根据您的示例，我可以看到您正在检查两列是否存在.如果已经有PRIMARY KEY，则只需在两列上添加UNIQUE约束.

In order to have this functionality working, you need to have a UNIQUE constraint on the table. Based on your example, I can see that you are checking on the two columns for its existence. If you already have a PRIMARY KEY, you can just add a UNIQUE constraint on both columns.

ALTER TABLE oc_product_attribute 
      ADD CONSTRAINT tb_uq UNIQUE(PRODUCT_ID, ATTRIBUTE_ID)

执行后，您现在可以使用INSERT..ON DUPLICATE KEY UPDATE

Once it has been executed, you can now use INSERT..ON DUPLICATE KEY UPDATE

INSERT INTO oc_product_attribute (PRODUCT_ID, ATTRIBUTE_ID, OtherCol, TEXT)
SELECT  MAX(PRODUCT_ID), MAX(ATTRIBUTE_ID), 1, 'XYZ'
FROM
        (
            SELECT  PRODUCT_ID, NULL ATTRIBUTE_ID 
            FROM    oc_product_description 
            WHERE   NAME = 'PRODUCT_NAME_HERE'
            UNION ALL
            SELECT  NULL PRODUCT_ID, ATTRIBUTE_ID 
            FROM    oc_attribute_description 
            WHERE   NAME='ATTRIBUTE_NAME_HERE'
        ) x
ON  DUPLICATE KEY UPDATE TEXT = 'ABC'

-- change OtherCol to the name of your column which you want to insert 1

• 在重复的键更新中插入...语法
• INSERT ... ON DUPLICATE KEY UPDATE Syntax

这篇关于在MySQL中使用IF语句在没有存储过程的情况下进行更新或插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！