插入引用另一个表的值 [英] Insert values referring to another table
问题描述
我创建了以下简单的datamodel
:
我使用以下insert statements
插入值:
1)表产品:
INSERT INTO test.products
(ProductName, Price)
VALUES
("Product A","99,99"),
("Product B","49,95"),
("Product C","5,95");
2)表订单:
INSERT INTO test.orders
(Customer)
VALUES
("Customer A"),
("Customer B"),
("Customer B");
到目前为止,所有这些都可以正常工作.
All this works fine so far.
但是,现在我也想在表Products_per_Order
中插入值.
如您在datamodel
中所见,Products_per_Order
表包含列Price
.在此列中,我要参考Products_idProducts
列中的 ID 插入价格.因此,我尝试使用以下insert statement
,但到目前为止无法正常工作:
However, now I also want to insert values into the table Products_per_Order
.
As you can see in the datamodel
the Products_per_Order
table contains the column Price
. In this column I want to insert the price referring to the ID in the column Products_idProducts
. Therefore, I tried to go with the following insert statement
but could not make it work so far:
INSERT INTO test.products_per_order
(Orders_idOrders, Products_idProducts, Price, Quantity)
VALUES
("1","1",(Select Price from test.products),"5"),
("1","2",(Select Price from test.products),"4"),
("2","1",(Select Price from test.products),"10"),
("3","2",(Select Price from test.products),"3"),
("3","3",(Select Price from test.products),"9");
您知道如何解决此问题吗?
Do you have any idea how to solve this issue?
推荐答案
我建议改用insert . . . select
:
insert into test.products_per_order (Orders_idOrders, Products_idProducts, Price, Quantity)
select x.idOrder, x.idProduct, p.price, x.qty
from (select 1 as idOrder, 2 as idProduct, 5 as qty union all
select 1 as idOrder, 2 as idProduct, 4 as qty union all
. . . -- I've left these out so you can see the structure of the query
select 3 as idOrder, 3 as idProduct, 9 as qty
) x left join
test.products p
on p.idProducts = x.idProduct;
这使错字导致错误价格的可能性大大降低.
This makes it much less likely that a typo will result in the wrong price.
这篇关于插入引用另一个表的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!