| |
Encounters a batch update needs, I intend to use an inline view updates + where in list processing techniques.
UPDATE (
SELECT / * + BYPASS_UJVC * / *
FROM mvbox_space.music_original t1
INNER JOIN (
SELECT REGEXP_SUBSTR (value_str, '[^,] +', 1, 1) AS p1, REGEXP_SUBSTR (value_str, '[^,] +', 1, 2) AS p2
FROM (
SELECT SUBSTR (inlist, INSTR (inlist, ';', 1, LEVEL) + 1, INSTR (inlist, ';', 1, LEVEL + 1) - INSTR (inlist, ';', 1, LEVEL) - 1) AS value_str, level AS l
FROM (
SELECT ';' || '20077,1; 20078,2' || ';' AS inlist
FROM DUAL
)
CONNECT BY LEVEL < = LENGTH ( '20077,1; 20078,2') - LENGTH (REPLACE ( '20077,1; 20078,2', ';', NULL)) + 1
)
) T2 ON t1.opus_id = t2.p1
)
SET visit_num = nvl (visit_num, 0) + p2, total_today = nvl (total_today, 0) + p2, total_this_week = nvl (total_this_week, 0) + p2, total_this_month = nvl (total_this_month, 0) + p2
10.2.0.1 through the test library.
But to get online 10.2.0.4, actually given, this internal HINT no effect.
Later rewritten as
MERGE INTO mvbox_space.music_original t1
USING (
SELECT REGEXP_SUBSTR (value_str, '[^,] +', 1, 1) AS p1, REGEXP_SUBSTR (value_str, '[^,] +', 1, 2) AS p2
FROM (
SELECT SUBSTR (inlist, INSTR (inlist, ';', 1, LEVEL) + 1, INSTR (inlist, ';', 1, LEVEL + 1) - INSTR (inlist, ';', 1, LEVEL) - 1) AS value_str, LEVEL AS l
FROM (
SELECT ';' || '20077,1; 20078,2' || ';' AS inlist
FROM DUAL
)
CONNECT BY LEVEL < = LENGTH ( '20077,1; 20078,2') - LENGTH (REPLACE ( '20077,1; 20078,2', ';', NULL)) + 1
)
) T2 ON t1.opus_id = t2.p1
WHEN MATCHED THEN UPDATE SET t1.visit_num = NVL (t1.visit_num, 0) + t2.p2, t1.total_today = NVL (t1.total_today, 0) + t2.p2, t1.total_this_week = NVL (t1.total_this_week, 0 ) + t2.p2, t1.total_this_month = NVL (t1.total_this_month, 0) + t2.p2 |
|
|
|