Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle inline view updates problems encountered     - Variables Python variables (Programming)

- Oracle10g 64-bit installation solution in Windows 2008 Server R2 (Database)

- Linux - use chroot command (Linux)

- Sniffer install applications in Linux supplement (Linux)

- Lazarus for Raspbian installation (Linux)

- Explore Android SQLite3 multithreading (Programming)

- Scope of variables in C # (Programming)

- Oracle Database routine inspection (Database)

- Httpclient4.4 of principle (Http execution context) (Programming)

- MySQL Basic tutorial: About varchar (N) (Database)

- How to choose the correct HTTP status code (Server)

- Hutchison DG standby database CPU consumption reached bottleneck repair (Database)

- Chkconfig set boot start under Linux (Linux)

- How to install with JSON support in Ubuntu 15.04 SQLite 3.9.1 (Database)

- Hadoop 2.7.1 installation configuration based on availability QJM (Server)

- Use OpenSSL for RSA encryption and decryption (Linux)

- How to extend / remove swap partitions (Linux)

- MySQL5.6 based GTID master-slave replication (Database)

- Archive log file size and redo log file size relationship (Database)

- Character Encoding notes: ASCII, Unicode and UTF-8 (Linux)

 
         
  Oracle inline view updates problems encountered
     
  Add Date : 2017-04-13      
         
         
         
  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
     
         
         
         
  More:      
 
- Ubuntu 14.04 users how to install VLC 2.2.0 (Linux)
- MariaDB 10.0.X, the dynamic column support JSON format to obtain data (Database)
- Linux command ls (Linux)
- Use web2py + uWSGI + Nginx Web server built on Linux (Server)
- Linux partition command (Linux)
- TypeScript basic grammar (Programming)
- Go powerful development server simple example (Server)
- SolrCloud-5.2.1 cluster deployment and testing (Server)
- Oracle 11g RAC automatically play GI PSU patch (11.2.0.4.8) (Database)
- Deploy Apache Spark cluster environment in Ubuntu (Server)
- CoreCLR compiled in Linux CentOS (Linux)
- Linux environment variable configuration and save places (Linux)
- DB2 commonly used scripting sort out (Database)
- How to add a new resolution VirtualBox (Linux)
- Oracle metadata Reconstruction experiments (Database)
- Ubuntu configuration SVN and http mode access (Server)
- Ubuntu server 8.04 Firewall Guide (Linux)
- MySQL & NoSQL - Memcached widget (Database)
- Kafka cluster deployment (Server)
- SecureCRT remote connection Ubuntu fails to solve the case (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.