Oracle updates to the same table

1. Description: The structure of table ta and table tb is identical. To update table ta with data in table tb,
2. Requirements: Take the ta as the standard. If the ta does not have data, the data in the tb is fully merged into the ta;
3. If the data in ta is incomplete and some fields are empty, update the ta table with the same id in tb,
4. --
5. Method 1: use full join, combined with nvl functions:
6. With taAs(
7. Select 1 id, 23 age,'Lilei'Name,'Ddd @ 126.com'Mail from dual union all
8. Select 2,Null,'Hanmeimei',NullFrom dual union all
9. Select 3, 23,Null,'Jim eee@153.com'From dual union all
10. Select 4, 22,'Tom',NullFrom dual ),
11. TbAs(
12. Select 1 id, 23 age,'Lilei'Name,'Bbb @ 126.com'Mail from dual union all
13. Select 2, 25,'Hanmeimei','Fff @ 124com'From dual union all
14. Select 5, 27,'Green','Ejorj @ 125.com'From dual)
15. Select nvl (ta. id, tb. id) id,
16. Nvl (ta. age, tb. age) age,
17. Nvl (ta. name, tb. name) name,
18. Nvl (ta. mail, tb. mail) mail
19. From ta full join tb
20. On ta. id = tb. id
21. Order by id;
22. ID AGE NAME MAIL
23. --------------------------------------------
24. 1 23 lilei ddd@126.com
25. 2 25 hanmeimei fff @ 124com
26. 3 23 jim eee@153.com
27. 4 22 tom
28. 5 27 green ejorj@125.com
29. --
30. Method 2: merge with merge:
31. Create table ta (id varchar2 (2), age number (3), name varchar2 (10), mail varchar2 (30 ));
32. Select * from ta;
33. ID AGE NAME MAIL
34. ----------------------------------------------
35. 1 23 lilei ddd@126.com
36. 2 hanmeimei
37. 3 23 jim eee@153.com
38. 4 22 tom
39. --
40. Create table tbAsSelect * from ta where 1 = 0;
41. Select * from tb;
42. ID AGE NAME MAIL
43. ----------------------------------------------
44. 1 23 lilei bbb@126.com
45. 2 25 hanmeimei fff@124.com.
46. 5 27 green ejorj@125.com
47. --
48. Merge into ta
49. UsingTb on (ta. id = tb. id)
50. When matched then
51. UpdateSet
52. Age = COALESCE (ta. age, tb. age ),
53. Name = COALESCE (ta. name, tb. name ),
54. Mail = COALESCE (ta. mail, tb. mail)
55. When not matched then
56. Insert (ta. id, ta. age, ta. name, ta. mail)
57. Values (tb. id, tb. age, tb. name, tb. mail );
58. --
59. ID AGE NAME MAIL
60. ----------------------------------------------
61. 1 23 lilei ddd@126.com
62. 2 25 hanmeimei fff@124.com.
63. 3 23 jim eee@153.com
64. 4 22 tom
65. 5 27 green ejorj@125.com
66. --
67. Method 3: Use update to directly update the ta table. If no data exists in the ta table, add the data in the tb table:
68. 3.1 update
69. Update ta
70. Set(A. age, a. name, a. mail) = (
71. Select nvl (a1.age, b1.age ),
72. Nvl (a1.name, b1.name ),
73. Nvl (a1.mail, b1.mail)
74. From ta a1, tb b1
75. Where a1.id = b1.id and a1.id = a. id)
76. Where exists (select 1 from ta a2 where a2.id = a. id );
77. // Note: This method fails. If you can find a solution, please kindly advise
78. 3.2 add
79. Insert into ta (id, age, name, mail)
80. Select tb. id, tb. age, tb. name, tb. mail
81. From tb
82. Where tb. id notIn(Select ta. id from ta );