CODEWITHSUNDEEP
sqldatabaseoracle-database
Rosey
Rosey

Reputation:

Is there a method to copy a new column in an existing table and copy the column info from other table in Oracle?

Can you please forward me the answer for my oracle project?

I have two different tables, invoice and customer. In the invoice table I already have a column called date and in the customer table I don't have the date column and I already created. I don't know how to bring the date data from the invoice table. Can anyone answer this question for me?

Upvotes: 0

Views: 193

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132700

It isn't entirely clear what you want, but adding and populating a column on a table is easy enough:

1) Adding a new column:

alter table customer add (some_date date);

(as duffymo has said, you can't have a column called "date" in an Oracle table - or at least, you shouldn't).

2) Populating a column from data in another table:

update customer c
set some_date = (select max(other_date) from invoices i
                 where i.customer_id = c.customer_id
                );

I used max() because I assume a customer may have more than one invoice. Of course, your needs may be different, but the important thing is that the subquery must only return 1 row for each customer somehow.

Upvotes: 1

duffymo
duffymo

Reputation: 308998

I think using keywords like "date" as column or table names is asking for trouble. It should be "creation_date" or "invoice_date" - more descriptive and not a keyword.

If that's correct, then the "date" in the customer table should be a "birth_date" or "signup_date" or something else meaningful for a customer, but NOT just a copy of "invoice_date". The DRY principle and normalization rules say you should not repeat data.

Upvotes: 1

Related Questions