CODEWITHSUNDEEP
mysqlstored-procedures
Him
Him

Reputation: 1

How to enter data to MySQL by using foreign key value?

I have 2 tables:

I have a product with:

product_id=1
product_name='Computer'
price=2500

How can I enter new data to table ORDERS (i.e. add a new order) by using product_name from table PRODUCT instead of entering product_id. I want to enter (for example in Stored procedures) product_name (f.e. 'Computer') and as output it ads a new row to table ORDERS with relevant product_id (i.e. 1)

If I use INSERT command to enter data into table ORDERS I need to write values as: (1,1,2)- order_id, product_id, quantity. However I want to write as: (1, 'Computer', 2), so that it automatically adds 1 instead of 'Computer' to table ORDERS.

Upvotes: 0

Views: 57

Answers (1)

Adarsh
Adarsh

Reputation: 726

If you have created it as product_id then the expectation is to supply the id itself at the time of insert, not value of that Id. One thing can be done here is to have a sub-query in your insert statement:

insert into ORDERS (order_id, product_id, quantity)
values (1, (select max(product_id) from PRODUCT where product_name='Computer'), 2);

The sub-query will fetch the product_id for your string passed. I have used max(product_id) just in case you have duplicates in the PRODUCT Table.

Note, if you are using a stored procedure then this can be done using a declared variable as two steps as well.

Upvotes: 0

Related Questions