Oracle uses concatenated strings to update a table

1. // Data
2. Ta
3. Col_1 col_2
4. -----------
5. A
6. B
7. C
8. D
9. // Result:
10. Col_1 col_2
11. -----------
12. A z0001
13. B z0002
14. C z0003
15. D z0004
16. //
17. Create table ta (
18. Col_1 varchar2 (2 ),
19. Col_2 varchar2 (7 ))
20. /
21. Insert into ta
22. Select'A',''From dual union all
23. Select'B',''From dual union all
24. Select'C',''From dual union all
25. Select'D',''From dual
26. /
27. // Solution 1:
28. Declare
29. Rn number: = 0;
30. Begin
31. ForClIn(Select col_1 from ta order by col_1) loop
32. Rn: = rn + 1;
33. Update ta
34. SetCol_2 ='Z'| Lpad (rn, 4,'0')
35. Where col_1 = cl. col_1;
36. Commit;
37. End loop;
38. End;
39. /
40. // Restore the table to its original state:
41. Update ta
42. SetCol_2 =''
43. Where col_1In('A','B','C','D');// Col_1 is not null;
44. // Solution 2:
45. Begin
46. ForCIn(Select rowid rid,
47. Row_number () over (order by col_1) rn
48. From ta)
49. Loop
50. Update ta
51. SetCol_2 ='Z000'| C. rn
52. Where rowid = c. rid;
53. End loop;
54. Commit;
55. End;
56. /
57. // Solution 3:
58. Create table tbAs
59. Select * from ta where 1 = 0
60. /
61. Insert into tb
62. Select col_1,'Z000'| Rn
63. From (
64. Select rownum rn, ta. col_1 col_1
65. From ta)
66. /