MySQL insert with value from select

Question

I have this query:

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)
)

I get an error:

Table 'oc_stock_hpp' is specified twice, both as a target for 'INSERT' and as a separate source for data

What should I do?

Does this answer your question? [Select, Modify and insert into the same table](https://stackoverflow.com/questions/9879830/select-modify-and-insert-into-the-same-table) — Aaron Morefield, Jan 29 '21 at 15:59
Why would you want to duplicate the most recent row for product_id? — P.Salmon, Jan 29 '21 at 16:06

score 4 · Accepted Answer · answered Jan 29 '21 at 15:59

4

You could use an insert-select statement and add the product_id to the query:

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

answered Jan 29 '21 at 15:59

Mureinik

297,002
52
306
350

MySQL insert with value from select

1 Answers1