code_junkie

Using a subquery instead of a table name in an Oracle Update Statement

Nov 11th, 2011
143
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.16 KB | None | 0 0
  1. UPDATE
  2. TABLE1 a,
  3. TABLE2 b
  4. SET
  5. a.COL1 = 'VALUE'
  6. WHERE
  7. a.FK = b.PK
  8. AND b.COL2 IN ('SET OF VALUES')
  9.  
  10. UPDATE
  11. (
  12. SELECT
  13. a.COL1
  14. FROM
  15. TABLE1 a,
  16. TABLE2 b
  17. WHERE
  18. a.FK = b.PK
  19. AND b.COL2 IN ('SET OF VALUES')
  20. ) update_tbl
  21. SET
  22. update_tbl.COL1 = 'VALUE'
  23.  
  24. UPDATE
  25. TABLE1 update_tbl
  26. SET
  27. update_tbl.COL1 = 'VALUE'
  28. WHERE
  29. EXISTS (
  30. SELECT
  31. 1
  32. FROM
  33. TABLE1 a
  34. TABLE2 b
  35. WHERE
  36. a.FK = b.PK
  37. AND b.COL2 IN ('SET OF VALUES')
  38. AND update_tbl.PK = a.PK
  39. )
  40.  
  41. UPDATE TABLE1 a
  42. SET a.COL1 = 'VALUE'
  43. WHERE a.FK IN
  44. ( SELECT b.PK FROM TABLE2 b
  45. WHERE b.COL2 IN ('SET OF VALUES')
  46. )
  47.  
  48. UPDATE
  49. (
  50. SELECT
  51. a.COL1, a.PKCOL
  52. FROM
  53. TABLE1 a,
  54. TABLE2 b
  55. WHERE
  56. a.FK = b.PK
  57. AND b.COL2 IN ('SET OF VALUES')
  58. ) update_tbl
  59. SET
  60. update_tbl.COL1 = 'VALUE'
  61.  
  62. UPDATE
  63. TABLE1
  64. SET
  65. COL1 = 'VALUE'
  66. WHERE
  67. ROWID in
  68. (
  69. SELECT
  70. a.rowid
  71. FROM
  72. TABLE1 a,
  73. TABLE2 b
  74. WHERE
  75. a.FK = b.PK
  76. AND b.COL2 IN ('SET OF VALUES')
  77. )
  78.  
  79. UPDATE
  80. TABLE1 update_tbl
  81. SET
  82. update_tbl.COL1 = 'VALUE'
  83. WHERE
  84. (update_tbl.PK1, update_tbl.pk2) in(
  85. select some_field1, some_field2
  86. from some_table st
  87. where st.some_fields = 'some conditions'
  88. );
  89.  
  90. DECLARE
  91.  
  92. /* Output variables to hold the result of the query: */
  93. a T1.e%TYPE;
  94. b T2.f%TYPE;
  95. c T2.g%TYPE;
  96.  
  97. /* Cursor declaration: */
  98. CURSOR T1Cursor IS
  99. SELECT T1.e, T2.f, T2.g
  100. FROM T1, T2
  101. WHERE T1.id = T2.id AND T1.e <> T2.f
  102.  
  103. FOR UPDATE;
  104.  
  105. BEGIN
  106.  
  107. OPEN T1Cursor;
  108.  
  109. LOOP
  110.  
  111. /* Retrieve each row of the result of the above query
  112. into PL/SQL variables: */
  113. FETCH T1Cursor INTO a, b;
  114.  
  115. /* If there are no more rows to fetch, exit the loop: */
  116. EXIT WHEN T1Cursor%NOTFOUND;
  117.  
  118. /* Delete the current tuple: */
  119. DELETE FROM T1 WHERE CURRENT OF T1Cursor;
  120.  
  121. /* Insert the reverse tuple: */
  122. INSERT INTO T1 VALUES(b, a);
  123.  
  124. /* Here is my stuff using the variables to update my table */
  125. UPDATE T2
  126. SET T2.f = a
  127. WHERE T2.id = c;
  128.  
  129. END LOOP;
  130.  
  131. /* Free cursor used by the query. */
  132. CLOSE T1Cursor;
  133.  
  134. END;
  135. .
  136. run;
Add Comment
Please, Sign In to add comment