MySQL insert with value from select

Question

我有这样的查询:

INSERT INTO oc_stock_hpp (product_id, buy_price) 
VALUES (
'3337',
(SELECT buy_price
FROM oc_stock_hpp
WHERE product_id ='3337'
ORDER BY id DESC
LIMIT 1)
)

我得到一个错误:

表'oc_stock_hpp'被指定两次，既作为'INSERT'的目标，又作为数据的单独源

我该怎么办?

Answer 1

您可以使用插入-选择语句并将product_id添加到查询中:

INSERT INTO oc_stock_hpp (product_id, buy_price) 
SELECT   product_id, buy_price
FROM     oc_stock_hpp 
WHERE    product_id = '3337'
ORDER BY id DESC LIMIT 1