使用外键值的Oracle Sql Update吗? [英] Oracle Sql Update with values using foreign key?

问题描述

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

如何更新表1，其第二列x1-x2在表2的fk1，fk2上分开

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

推荐答案

使用此设置:

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);

以下更新将使用table2的值刷新"列table1.diff:

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);

仅刷新需要更新的行(由于where子句).

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

这篇关于使用外键值的Oracle Sql Update吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！