1

I'm starting to using SQL statements in my Oracle database XE (tables and relations made by me) and I'm having a hard time to understand how can I put a column from another table.

The example in this case is:

  • I have two tables that are interconnected, ORDERS and PRODUCT.

In ORDERS there are the following attributes:

  • ORDERID (defined as Primary Key), PRODUCT_NAME (a Foreign Key from the PRODUCT table) and QUANTITY.

In PRODUCT there are:

  • NAME (defined as Primary Key) and PRICE.

I want to add a column that gets the PRICE from the PRODUCT table and insert it to the ORDERS table according to the PRODUCT_NAME, obviously.

How can I do it? Are there any workarounds?

Please, keep in mind that I'm a newbie and my knowledge is very limited.

6
  • Are you using an Order_Line table - with Product_Name, Quantity and Product_Price? The Orders table should be a header. Note use of singular names for tables - except for Orders (Order being an SQL keyword). – Vérace Apr 29 '15 at 19:28
  • Sorry, I don't know what you mean with Order_Line – Enthusiast Apr 29 '15 at 19:29
  • CREATE TABLE Order_Line (Order_id, Product_Name, Quantity) Put a UNIQUE index on Order_id, Product_Name. CREATE TABLE Orders(Order_id, Client_id, Order_Date,..... perhaps discount or other fields) PRIMARY KEY for Orders is Order_id). Just a suggestion. – Vérace Apr 29 '15 at 19:34
  • Oh, I see, I have another table with this content, but with another name that we are used to use in our company. Thanks for the advice. – Enthusiast Apr 29 '15 at 19:35
  • Since nobody is answering I found a workaround for now here stackoverflow.com/questions/2446764/… but I have to run this script each time there is an INSERT in the ORDERS table. My goal is making this as automatic as possible. – Enthusiast Apr 30 '15 at 17:29
2

The original question was:

I want to add a column that gets the PRICE from the PRODUCT table and insert it to the ORDERS table according to the PRODUCT_NAME, obviously. How can I do it? Are there any workarounds? Please, keep in mind that I'm newbie and my knowledge is very limited.

and the ultimate reply (after much communication through comments - see above), my response was

And you can't use triggers to make updating automatic? I'd say they are the way to go!

Which worked.

-2

you add column to orders table using

alter table tablename  add  column  datatype

then

insert  into  orders(price) select  price from  product;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.