MySQL-将主键从一个表插入到另一个表(外键) [英] MySQL - Inserting Primary Key from one table to another (Foreign Key)
问题描述
尽管已经提出了这个问题,但由于我不断收到错误,它并未真正适应我希望它为我工作的方式.
Although this question has been asked, It hasn't really adapted to how I wanted it to work for me, as i keep receiving errors.
我有一张桌子:Customer_orders,我将值插入到其中:
I have a table: Customer_orders which i insert values into:
<sql:update var="count">
insert into customer_order (order_date,delivered ,shipping_date,customer_number )
values ( SUBDATE(NOW(), INTERVAL 17 DAY), 1, SUBDATE(NOW(), INTERVAL 14 DAY), <%= session.getAttribute( "username" ) %> );
</sql:update>
现在,这个customer_order具有称为订单号"的主键,该键可以自动递增
Now this customer_order has PRIMARY KEY called "Order Number" that auto increments
我想使用这个order_number并将其插入到表中,我尝试过类似的操作:
I want to take this order_number and insert it into a table, I tried something like:
<sql:update var="count">
insert into order_item (item_code,value,order_number,quantity)
values( "<%= request.getParameter( "item_code" ) %>", "<%= request.getParameter( "item_price" ) %>",customer_order(order_number),1 );
</sql:update>
我什至尝试了不同的方法来插入order_number,但是我无法使其正常工作.当我创建两个表时,order_number是第二个表中的外键,因此我认为它会自己获取值,但不会.
And i even tried different ways to insert the order_number but I can't get it to work. When I created the two tables, the order_number is a foreign key in the 2nd table so I thought it would grab the value itself but didn't.
我在做什么错了?
推荐答案
您可以简单地从事务中的第一个查询中获取最后插入的ID,然后根据需要将其插入另一个表中,因此我建议采用这种方法它可能会帮助您:
you can simply get the last inserted id from your first query in a transaction and then insert it in another table as you like , so for that i propose this approach it may help you :
<sql:transaction dataSource="${snapshot}" >
<sql:update var="count">
insert into customer_order (order_date,delivered ,shipping_date,customer_number )
values ( SUBDATE(NOW(), INTERVAL 17 DAY), 1, SUBDATE(NOW(), INTERVAL 14 DAY), <%= session.getAttribute( "username" ) %> );
</sql:update>
<sql:query var="las_inserted_id" >
SELECT LAST_INSERT_ID() as last_Id
</sql:query >
/*now you got the last inserted id so that you can simply insert it in your new query as you like*/
<sql:update var="count">
insert into order_item (item_code,value,order_number,quantity)
values( "<%= request.getParameter( "item_code" ) %>", "<%= request.getParameter( "item_price" ) %>",${ las_inserted_id.rows[0].last_Id },1 );
</sql:update>
</sql:transaction>
NB :请尽您最大努力避免视图中的sql逻辑代码!这样可以轻松维护您的应用程序
NB : please try as much as you can to avoid sql logic code in your view !! so that it could be easy to maintain your application
这篇关于MySQL-将主键从一个表插入到另一个表(外键)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!