How Can I compare a Column in a Self Join with itself if it is Not Null?

Question

I'm doing an update to a temp table in Oracle. My thinking was that I could use NVL to set a value ( essentially making the null value on either side equal )

What happens in the update is that if a number of comparisons hold true then a column is not updated, one of those compared columns can be null and Oracle does not compare null values.

The Code may make it easier to understand.

UPDATE Temp_Table A Set T.ColZ = 'something'
WHERE NOT EXISTS
( SELECT * FROM Temp_Table B
WHERE A.ColF = B.ColF
AND ...more column comparisons
AND ((NVL(A.ColC, 'N/A') = NVL(B.ColC, 'N/A'))
     AND more column Comparisons)
);

The update works when ColC contains no Nulls but when I have a row that ColC is Null then it gets updated despite being the same on both sides.

I had also tried using a subquery in the where clause but with no luck. Would a subquery be a better method to use here?

Any help or hints are greatly appreciated.

Just wanted to add that ideally I would like to skip the comparison altogether if that col is null so any ideass on how to do it that way are welcome. Thanks

A per Ypers request an example with a few rows... hopefully it is clear enough

Temp_Table
Col1 | Col2 | Col3 | Col4 |Col5 |Col6
AB   |  DC  |  EF  |  GH  | 08  |
BA   |  CD  |  EF  |  GH  | 08  |
AB   |  DC  |  HI  |  NULL| 05  |
AB   |  DC  |  JK  |  LM  | 04  |

In the above table row 1 matches with row 2. My update is supposed to single out rows like 3 and 4 where there is no corresponding row. I can get it to work when Col4 is not Null but it fails when that value is Null. Just FYi in the temp_table any Col2 that has DC value Col1 will have AB as its value and the for any CD value in Col2 then Col1 has BA as its value.

UPDATE Temp_Table T SET Col6 = 'Unmatched'
WHERE NOT EXISTS
( SELECT * FROM Temp_Table B
 WHERE T.Col3 = B.Col3
 AND   T.COl4 = B.Col4
 AND   T.COl5 = B.Col5 )

Hope that helps and thanks.

There's nothing obviously wrong with what you've posted; the problem is probably elsewhere. Can you post a small, complete `temp_table` and a small, complete `UPDATE` statement that demonstrates the problem? — ruakh, Mar 06 '12 at 22:53
@ypercube: Note the `WHERE NOT EXISTS`. The point is that the subquery *should* find records in `Temp_Table B` matching the records in `Temp_Table A` even if that field is `NULL`, but the OP believes that it doesn't work. — ruakh, Mar 06 '12 at 22:54
Updated the main post as per Ypers and Ruaks requests for clarity. — dee, Mar 07 '12 at 15:06

ypercubeᵀᴹ · Accepted Answer · 2012-03-07T17:16:48.483

2

If I understand correctly, you want to change:

((NVL(A.ColC, 'N/A') = NVL(B.ColC, 'N/A'))

to:

(A.ColC = B.ColC OR A.ColC IS NULL OR B.ColC IS NULL)

After the question's update.

You could try this:

UPDATE Temp_Table T 
SET Col6 = 'Unmatched'
WHERE NOT EXISTS
( SELECT * 
  FROM Temp_Table B
  WHERE T.Col3 = B.Col3
    AND T.COl4 = B.Col4
    AND T.COl5 = B.Col5

    AND NOT (  T.Col1 = B.Col1    --- all the other columns except Col6
           AND T.Col2 = B.Col2
            )
)

edited Mar 07 '12 at 17:16

answered Mar 06 '12 at 22:55

ypercubeᵀᴹ

113,259
19
174
235

Thanks for the answer, unfortunately, I can't seem to make this work the way that it needs to. Ruakh is correct btw when i he says that where these matches don't exist is where the update happens. Not sure if that helps clear things up or not. I'll keep working on it. Thanks. – dee Mar 07 '12 at 14:17
@dee: If you can locate the issue to an example with a few rows, it would help us understand what the issue is exactly. – ypercubeᵀᴹ Mar 07 '12 at 14:24
I made a small temp_table example in my post... not sure if it helps to clarify. Thanks – dee Mar 07 '12 at 14:54
@dee: OK, I think I understand now. Does the table have a Primary or Unique Key? – ypercubeᵀᴹ Mar 07 '12 at 15:36
No it has no pk or fk ... it's a temp table in a stored proc, if I'm not mistaken, temp tables in oracle do automatically have a rowid however. not sure if we can link them up that way or not. I'm currently working a with a merge statement to see if that works better. – dee Mar 07 '12 at 16:19
tried it and it works although I switched essentially to a merge statement so no I'm updating when it matches to one thing and when it does not match to something else. Thanks for all your help and insight. – dee Mar 07 '12 at 17:44

How Can I compare a Column in a Self Join with itself if it is Not Null?

1 Answers1