CODEWITHSUNDEEP
sql
Longstride83
Longstride83

Reputation: 1

Multiply 2 columns from 2 seperate tables and add the result into a seperate column

I am new to this and its frying my brain.

I have 2 tables a product table name,unit price and and order table which has quantity and total price. I have this so far but am unable to figure out how to get the result into an existing table.

SELECT a.product_name, (a.product_unit_price * b.order_quantity)
FROM t_product a, t_order_details b,
Where a.product_name = b.t_product_product_name;
);

What can I do to get the multiplication result added to the order total price column in the order details table?

Upvotes: 0

Views: 51

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656734

Errors are the comma before Where and the dangling ) ;.

But use a clearer explicit JOIN for the SELECT instead:

SELECT a.product_name, (a.product_unit_price * b.order_quantity)
FROM   t_product a
JOIN   t_order_details b ON a.product_name = b.t_product_product_name;

What can I do to get the multiplication result added to the order total price column in the order details table?

You probably mean to UPDATE? (Postgres syntax):

UPDATE t_order_details AS b
SET    total_price =  a.product_unit_price * b.order_quantity
FROM   t_product AS a
WHERE  a.product_name = b.t_product_product_name;

Or the same with a correlated subquery (should work in most RDBMS):

UPDATE t_order_details AS b
SET    total_price =  (SELECT a.product_unit_price * b.order_quantity
                       FROM   t_product AS a
                       WHERE  a.product_name = b.t_product_product_name);

Upvotes: 1

Related Questions