Oracle Query : Single update statement

Question

I need to code an Oracle Query for the below logic and any help is appreciated.

I have a table with 8 columns and out of that need to consider 3 column for the a specific business logic.

Table data (with the 3 columns)

A               B             C 

071699          01            I

071699          01W     

071699          02W     

071699          01W           I

071699          02W

more rows.

Amount of data varies depending upon case, meaning it could be one or more rows per column A-B combination and usually out of these column C is populated for at least 1 combination.

This table has over 100K of distinct A values.

Logic I need to implement:

Check - for a specific A value, how many combinations we have (A-B). For a specific A: Check if any combination (A-B) is populated with column C data.
Take the value from the populated C column and update the same table (for the other combination of same A)

Data before (only showing specific rows)

A               B             C 

071699          01            I

071699          01W     

071699          02W

Data After Query

A               B             C  

071699          01            I

071699          01W           I

071699          02W           I

I have a SQL server query doing this logic in a single query but not working in Oracle and I am getting error,"Single row query returning more than one row"

SQL Server Query

update c 
set c.colC = u.colC
from data_table u
join data_table c on u.colA = c.colA and u.colB <> c.colB
    and u.colB = (select MIN(colB) from data_table  
                    where colA = u.colA and colC is not    null)
and u.colC is not null and c.colC is null

Any help is appreciated to write similar oracle version.

check this [UPDATE](http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join) — Rock 'em, May 22 '14 at 12:31
Thank you for the suggestions. I tried with the "IN" and query is not working and still giving me errors. For the other link, it gives syntax for the update statement which is very generic and not give any specifics of this issue. I'll appriciate if you can provide any suggestions specific to this issue. — Amit Arora, May 23 '14 at 02:43
Can there be two different values in column C for the same combination of Column A and Column B? If yes, Which value would you take to update? — A Nice Guy, May 23 '14 at 08:46
No, that is not the case, though it is determined by the other columns which has stat and end date. Assuming it is the same, how we can convert this SQL server query into oracle. — Amit Arora, May 23 '14 at 11:58

score 0 · Answer 1 · answered May 24 '14 at 08:10

0

Oracle doesn't allow joins in update queries unless you have a unique column which guarantees a 1-1 mapping which definitely doesn't apply in your case. So about the best you can do here is a nested subquery. It ain't pretty but it will work. I wasn't able to completely match up the logic you said you needed to implement with the logic that was in the SQL Server update statement, so I went with the statement.

UPDATE data_table u
SET u.COLC =(
  SELECT  c.ColC
  FROM    data_table c
  WHERE   c.ColA = u.ColA
    and   c.ColB =(
        SELECT  MIN( ColB )
        FROM    data_table
        WHERE   ColA = c.ColA
          AND   ColC IS NOT NULL
      )
)
where u.ColC is null;

answered May 24 '14 at 08:10

TommCatt

5,498
1
13
20

This will not work in my case as inner query does return multiple rows. – Amit Arora May 25 '14 at 02:47
Yes, the inner query returns multiple rows. But what is it about the Update that will not work? I tested it on the data you provided. – TommCatt May 25 '14 at 03:06
The inner query will return more than one row, for example - this table has about 100k distinct colA and for each colA - we can have multiple colB, so for 1 colA we can have multiple colA-ColB combinations and each combination can have null or specific value for the colC. In this scenario - inner query will may give multiple rows output, but if we put distinct combination of colA-ColC in separate table and then update in a separate statement then it will work for all the possible scenarios. Let me know if you have any specific questions. – Amit Arora May 26 '14 at 00:14
I cannot resolve what you're saying now with the example data you show above. You have three rows with the same value in A, all different values in B. You show the desired result as all the A values with NULL C values getting the non-NULL C value regardless of B values. – TommCatt May 26 '14 at 03:14

score 0 · Answer 2 · answered May 25 '14 at 02:52

I tried various ways to do this task in a single query but not able to achieve that due to Oracle limitations, below is the solution worked for me:

I split the query into two parts - one doing insert statement and another with update.

First query : Here I am inserting the rows into another temp table, logic - get distinct rows per colA - ColC where colC is polulated.
Use the tmp table created in the step 1, to update the main data table by join on colA.

If anyone has better solution, then please send your response and I'll surely try it.

score 0 · Accepted Answer · answered Jun 05 '14 at 14:33

I resolved this issue with the set based coding after trying various methods like spliting into two tmp tables, cursor, etc, below is the sample code :

This query is 3-5X faster than any other solution.

   UPDATE data_table a SET C = (
        WITH comp AS (
        SELECT  DISTINCT A, C FROM data_table a 
        WHERE B = (SELECT MIN(B) FROM data_table
                              WHERE A =  a.A
                              AND C IS NOT NULL)
                    )
         SELECT 
            CASE
                when a.C IS NULL  THEN c.C
                when a.C IS NOT NULL THEN a.C
            END    
         FROM comp c 
         WHERE a.A = c.A AND c.C IS NOT NULL  
     );

Oracle Query : Single update statement

Data before (only showing specific rows)

3 Answers3