|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 9th, 2005, 06:02 AM
|
||||
|
||||
|
Adding column and populating from existing FK
Hello everyone. I hope someone can help me with this one. I have three tables:
(pseudo-code) TABLE PROJECTS { COLUMN id COLUMN company FK to COMPANIES.ID ... } TABLE COMPANIES { COLUMN id ... } TABLE COST_ITEMS { COLUMN id COLUMN project FK to PROJECTS.ID ... } In the last table COST_ITEMS I want to add a new column with a foreign key to COMPANIES (I know, I know, but apparently now it CAN be assigned to companies other than the project's). So, how do I auto-populate the new column with the value currently associated to the project? I'm doing something like: Code:
ALTER TABLE COST_ITEMS ADD company NUMBER;
UPDATE COST_ITEMS SET company = (
SELECT com.id FROM COMPANIES com, PROJECTS pro, COST_ITEMS cst
WHERE cst.project = pro.id AND pro.company = com.id);
ALTER TABLE COST_ITEMS MODIFY company NUMBER NOT NULL;
Obviously the UPDATE statement isn't working, but I have no idea how to do it. Can anyone help?
|
|
#2
November 9th, 2005, 08:55 AM
|
|||
|
|||
|
Haven't tested it, but shouldn't that be:
Code:
UPDATE cost_items
SET company = (SELECT com.id
FROM companies com,
projects pro
WHERE cost_items.project = pro.id
AND pro.company = com.id);
|
|
#3
November 9th, 2005, 09:27 AM
|
||||
|
||||
|
Quote:
No, that's correct - the project->legal entity and cost item->project relations are 1 to 1, so it will always return a single row. Thank you so much for that!  
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Adding column and populating from existing FK |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
