CODEWITHSUNDEEP
sqloracle-databaseplsqlcursorupdates
user1368271
user1368271

Reputation:

Update single column of different data types

I have three tables in Oracle database: CONTENT, CONTENT_DATA , DATA_TYPE

Data records in CONTENT_DATA are as follows:

PK_ID  DATA_TYPE_ID  CONTENT_ID   VALUE
1         1             1           0
2         2             1          100
3         3             1          200

I need to update 'value' column to sum of other 'value' columns according to their DATA_TYPE_ID in CONTENT_DATA table and this process should be done for each of CONTENT_ID. For example: VALUE(DATA_TYPE(3)) = SUM(VALUE(DATA_TYPE(1)), VALUE(DATA_TYPE(2)))

I created the procedure which is shown below. But it runs very slow. Are there any better option to handle such a problem?

CREATE OR REPLACE PROCEDURE UPDATE_DATA
AS
    CURSOR your_cursor is
        SELECT DISTINCT CONTENT_ID FROM CONTENT_DATA;
    item your_cursor%rowtype;
BEGIN
    OPEN your_cursor;
    loop
        FETCH your_cursor INTO item;
        UPDATE CONTENT_DATA SET VALUE = 
             (SELECT  SUM(NVL(VALUE,0)) FROM CONTENT_DATA WHERE DATA_TYPE_ID IN(1,2)
                                        AND CONTENT_ID = item.CONTENT_ID) 
        WHERE CONTENT_ID = item.CONTENT_ID AND DATA_TYPE_ID = 3;  
    
        UPDATE CONTENT_DATA SET VALUE = (SELECT  SUM(NVL(VALUE,0)) FROM CONTENT_DATA WHERE DATA_TYPE_ID  IN(3,4,5,6,7,8,9)
                                        AND CONTENT_ID = item.CONTENT_ID)
        WHERE CONTENT_ID = item.CONTENT_ID
        AND DATA_TYPE_ID = 10;           
        
        
        UPDATE CONTENT_DATA SET VALUE = (SELECT  SUM(NVL(VALUE,0)) FROM CONTENT_DATA WHERE DATA_TYPE_ID IN( 10,11,12,13,14)
                                            AND CONTENT_ID = item.CONTENT_ID)
        WHERE CONTENT_ID = item.CONTENT_ID
        AND DATA_TYPE_ID = 15;
    
    end loop;
    close your_cursor;
END;

Upvotes: 0

Views: 55

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

I don't think you need to use cursors for this. I built a test case for you to see how it behaves, then you only need to change your three updates in the cursors for just three statements, without using cursors

Test Case

SQL> create table my_merge_test ( pk_id number, data_type_id number, content_id number, value number );

SQL> select * from my_merge_test ;

     PK_ID DATA_TYPE_ID CONTENT_ID      VALUE
---------- ------------ ---------- ----------
         1            1          1          0
         2            2          1        100
         3            3          1        200
         4            3          1        120
         5            3          1        120

Now, let's see what you want to do on the first update:

UPDATE CONTENT_DATA SET VALUE = 
             (SELECT  SUM(NVL(VALUE,0)) FROM CONTENT_DATA WHERE DATA_TYPE_ID IN(1,2)
                                        AND CONTENT_ID = item.CONTENT_ID) 
        WHERE CONTENT_ID = item.CONTENT_ID AND DATA_TYPE_ID = 3;

For each content_id where data type is 1 or 2, you want to update the value to the sum of the value column ( 0 in case of null ) when the items are the same and data type is 3. For the others update the logic is the same , but with different values

Instead of using cursors, you can do the operation in one statement using with clause

SQL> update my_merge_test t
  2  set t.value = (
  3    with source as ( select  content_id, SUM(NVL(value,0)) as sum_value from my_merge_test where DATA_TYPE_ID IN(1,2)
                   group by  content_id
        )
  select h.sum_value
  from  source h
  where h.content_id = t.content_id and t.data_type_id = 3
) where t.data_type_id = 3
;  4    5    6    7    8    9   10

3 rows updated.

SQL> select * from my_merge_test ;

     PK_ID DATA_TYPE_ID CONTENT_ID      VALUE
---------- ------------ ---------- ----------
         1            1          1          0
         2            2          1        100
         3            3          1        100
         4            3          1        100
         5            3          1        100

SQL>

Then the second update would be

update my_merge_test t
set t.value = (
  with source as ( select  content_id, SUM(NVL(value,0)) as sum_value from my_merge_test where DATA_TYPE_ID IN (3,4,5,6,7,8,9)
               group by  content_id 
    )
  select h.sum_value 
  from  source h
  where h.content_id = t.content_id and t.data_type_id = 10
) where t.data_type_id = 10 
;

And the third would be

update my_merge_test t
set t.value = (
  with source as ( select  content_id, SUM(NVL(value,0)) as sum_value from my_merge_test where DATA_TYPE_ID IN ( 10,11,12,13,14)
               group by  content_id 
    )
  select h.sum_value 
  from  source h
  where h.content_id = t.content_id and t.data_type_id = 15
) where t.data_type_id = 15 
;

Replace the names with your tables and let me know if it does what you need.

Upvotes: 1

Related Questions