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屋！