Oracle Sql Update with values using foreign key?

Question

Table 1:
Name, x1-X2, fk1, fk2.
Table 2:
K1(parent for table 1),X

How to update table 1 whose second column x1-x2 depands on fk1,fk2 from Table 2

Table1:
a,1.0,1,2
b,-3.0,2,3

Table 2
1,4.0
2,5.0
3,2.0

Answer 1

Not quite sure I understand the question, referential integrity constraints do not prevent you from updating a table. If you need to do a lot of work in a transaction you can look into Deferred Constraints. Is there any chance you could clarify what you mean?

Answer 2

Not sure exactly what the problem is, maybe you need to rephrase the question a little.

In general, the foreign key constraint makes sure that there exists a corresponding row in the referenced table.

When you update a row with a foreign key, and try to set a value that does not point to such a master row, you will get an error, either immediately or when you commit (depending on when the constraint is enforced, it can be deferred).

Other than that, the update is no different than any other update.

Answer 3

Since the data in table 1 is dependent on the data in table 2 you won't be able to "Update" table 1 directly.

You will have to perform the update on table 2 and then recalculate table 1.

You could do it inside a transaction or perhaps a trigger on table 2.

Another option may be to have table one only hold the foreign keys and the name and then create a view that calculates the X1-X2 value.

EDIT

After looking at the sample data, I don't think you can unambiguously have table 2 be updates as a result of an update on table 1.

For example if you update the second column of table 1 to be 43, how would you know what values to set the specific rows of table 2 (it could be 40 and 3, 20 and 23, etc.)

Answer 4

With this setup:

CREATE TABLE table2 (k NUMBER PRIMARY KEY, x NUMBER);
CREATE TABLE table1 (
   NAME VARCHAR2(10) PRIMARY KEY, 
   diff NUMBER, 
   fk1 NUMBER REFERENCES table2, 
   fk2 NUMBER REFERENCES table2);

the following update will "refresh" the colum table1.diff with the values of table2:

SQL> UPDATE (SELECT child.diff old_diff, parent2.x - parent1.x new_diff
  2            FROM table1 child
  3            JOIN table2 parent1 ON (child.fk1 = parent1.k)
  4            JOIN table2 parent2 ON (child.fk2 = parent2.k))
  5     SET old_diff = new_diff
  6   WHERE old_diff != new_diff
  7      OR (old_diff IS NULL AND new_diff IS NOT NULL)
  8      OR (old_diff IS NOT NULL AND new_diff IS NULL);

Only the rows that need updating will be refreshed (thanks to the where clause).