Is it possible to take a value from a row in one table in my MySQL database and insert into another table in the same database?

Question

So say I have a products table with a list of products, and one of the fields is price. This table also has an id field. When someone makes a purchase I insert a row into another table called purchases. This table also has a field called price and a productid field that maps to the products table id field. I want to know if I can get the price from my products table and insert it into the purchases table all in one SQL query.

Is this possible in MySQL?

Thanks!

Answer 1

possible:

insert into purchases(purchase_id, product_id, price) 
select 88, product_id, current_price from product 
where product_id = 'KEYB'

to include the current discount:

insert into purchases(purchase_id, product_id, price, discount) 
select 88, product_id, current_price, current_discount from product 
where product_id = 'KEYB'

Answer 2

INSERT INTO purchases (
    price
    ,productid
    ,add your other columns here
)
SELECT price
    ,id
    ,add your other columns here
FROM products
WHERE add your selection criteria here

Answer 3

Sure. In my normal practice I'd just be sending the price as part of the insert, but if you truly need to extract it from the product table at that point, you can do it with a subselect, like:

INSERT INTO `purchase`
SET `product_id` = $whatever,
`price` = (
    SELECT `price`
    FROM `product`
    WHERE `id` = $whatever
)