mysql if语句只能在存储过程中使用,在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

Here is my code:

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

I get this error with the above:

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

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

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

解决方案

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

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)

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