Oracle数据库的表同步以及多表关联update语句

Oracle中的update与select配合使用

在Oracle数据库我们可以使用下面格式的update和select配合使用，用一张表的值更新另外一张表。

UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)

或

UPDATE A SET A1=(SELECT B1 FROM B WHERE A.ID = B.ID),
A2=(SELECT B2 FROM B WHERE A.ID = B.ID),
A3=(SELECT B3 FROM B WHERE A.ID = B.ID)

Oracle使用update语句注意事项
在SQL Server中，可以使用下面的update语句进行字段值的更新：

UPDATE A SET A1 = B1, A2 = B2, A3 = B3 FROM A,B WHERE A.ID = B.ID

这个SQL语句其实是先进行表连接，然后再根据表中的数据进行更新。但是Oracle不支持这种UPDATE...SET...FROM...WHERE...的语法。
在 SQL 中，表连接（left join、right join、inner join 等）常常用于 select 语句，其实在 SQL 语法中，这些连接也是可以用于 update 和 delete 语句的，在这些语句中使用 join 还常常得到事半功倍的效果。
如果Oracle想要实现上述语句的功能，可以使用下面的写法：

UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID) 
WHERE ID IN (SELECT B.ID FROM B WHERE A.ID = B.ID)

这里其实是先用where语句过滤筛选出需要update的行，然后再进行更新。如果舍弃where条件，则就会对在子查询(SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID) 中没有查询结果的记录字段赋予NULL值，这样的话就和上面那条SQL语句语义不一致。

用一张表的值更新另外一张表可能遇到的问题

（1）使用两表(多表)关联update -- 被修改值由另一个表运算而来

假设有下面两张表，其中mobile_no为手机型号，mobile_name为手机名称，screen为手机分辨率。

现在要根据mobile_no把Table2中的screen的值填充到Table1中的screen字段中。可以使用下面的SQL语句：

UPDATE Table1 t1 -- 使用别名
SET screen = (SELECT screen FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)
WHERE EXISTS(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no);

上述SQL的执行结果为：

注意在这个语句中，

(SELECT screen FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)

与

(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)

是两个独立的子查询。通过Table2来更新Table1的相应字段。执行SQL语句时，系统会从Table1中一行一行读记录，然后再通过关联子查询，找到相应的字段来更新。
如果舍弃WHERE条件，则默认对Table1进行全表。但是这样会产生一个问题：如果不加(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)的话，Table1表中那些在Table2中无对应记录的screen值就全被改为NULL了。比如在这里Table1中的mobile_no=小米的记录在Table2中没有对应的字段，若舍弃WHERE条件，则最后的SQL语句执行结果就变成：

可以看到原本mobile_no=小米的记录的screen是有值的，执行外不带(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)的UPDATE语句之后screen变成了NULL。

（2）使用NVL函数解决UPDATE时候遇到空值的尴尬

假设有下面两张表，其中mobile_no为手机型号，mobile_name为手机名称，screen为手机分辨率。在Table1中，screen的值不能为空。

现在要根据mobile_no把Table2中的screen的值填充到Table1中的screen字段中。如果我们用以下的SQL语句：

UPDATE Table1 t1 -- 使用别名
SET screen = (SELECT screen FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no);

则执行时会报如下错误：

01407, 00000, "cannot update (%s) to NULL"
// *Cause:
// *Action:

这是因为Table1中的mobile_no=小米的记录在Table2中没有对应的字段，则系统会把NULL值赋给screen字段，而screen字段又不能为空，所以就会报上述错误。
要解决这种尴尬的情况，我们可以使用Oracle的NVL函数：

UPDATE Table1 t1 -- 使用别名
SET screen = NVL(
   (SELECT screen FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no),t1.screen);

（3）解决UPDATE时子查询返回多条结果的问题

case1：返回的多条查询结果是相同的
假设有下面两张表，其中mobile_no为手机型号，mobile_name为手机名称，screen为手机分辨率。在Table2中，存在两条screen完全相同的mobile_no为HTC的记录。

现在要根据mobile_no把Table2中的screen的值填充到Table1中的screen字段中，如果我们（1）中的SQL语句，则执行时会报如下错误：

01427, 00000, "single-row subquery returns more than one row"
// *Cause:
// *Action:

这是因为当mobile_no='HTC'的时候，(SELECT screen FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)子查询返回了两条查询结果。此时我们可以使用distinct/group by去除重复值来得到正确的执行结果：

#使用distinct去重
UPDATE Table1 t1 -- 使用别名
SET screen =
   (SELECT distinct(screen) FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)
WHERE EXISTS(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no);

#使用group by去重
UPDATE Table1 t1 -- 使用别名
SET screen = 
   (SELECT screen FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no group by screen)
WHERE EXISTS(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no);

case2：返回的多条查询结果是不同的
假设有下面两张表，其中mobile_no为手机型号，mobile_name为手机名称，screen为手机分辨率。在Table2中，存在两条screen完全相同的mobile_no为HTC的记录和两条screen不同的mobile_no为HUAWEI的记录。

现在要根据mobile_no把Table2中的screen的值填充到Table1中的screen字段中，若同一机型有两个不同的screen，则插入记录之后上网查询该机型的分辨率然后进行手动修改。
此时即使用case1中的SQL语句，也会报子查询返回多条查询结果的错误，因为Table2中mobile_no为HUAWEI的screen有两个不同的值，所以无法去重。在这种情况下有以下几种解决办法：

A. 使用rownum限定只更新返回一条查询结果的记录

UPDATE Table1 t1 -- 使用别名
SET screen = 
  (SELECT screen FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no AND and rownum=1)
WHERE EXISTS(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no);

B. 使用Oracle的WMSYS.WM_CONCAT函数合并列
Oracle数据库中WMSYS.WM_CONCAT(column)函数的作用是将多行合并为一行，如一个人买了3只股票A,B,C 正常我们会记录成三条数据，现在要以一条数据显示该人的股票，就可以使用此函数。

UPDATE Table1 t1 -- 使用别名
SET screen = 
  (SELECT WMSYS.WM_CONCAT(screen) FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)
WHERE EXISTS(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no);

上述SQL语句的执行结果为：

然后我们就可以手动修改包含多条记录的screen值。

C. 使用聚合函数
MAX()和MIN()函数不仅可以作用于数值型数据,也可以作用于字符串或是日期时间数据类型的数据。利用这个特性，我们可以使用聚合函数让查询结果只剩一条。

UPDATE Table1 t1 -- 使用别名
SET screen = 
  (SELECT MAX(screen) FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no)
WHERE EXISTS(SELECT 1 FROM Table2 t2 WHERE t1.mobile_no=t2.mobile_no);