CODEWITHSUNDEEP
oracle-databaseplsql
jlrolin
jlrolin

Reputation: 1614

PL/SQL: UPDATE inside CURSOR, but some data is NULL

I'm still learning some of the PL/SQL differences, so this may be an easy question, but... here goes.

I have a cursor which grabs a bunch of records with multiple fields. I then run two separate SELECT statements in a LOOP from the cursor results to grab some distances and calculate those distances. These work perfectly.

When I go to update the table with the new values, my problem is that there are four pieces of specific criteria.

update work
        set kilometers = calc_kilo,
            kilo_test = test_kilo                                  
        where lc = rm.lc
        AND ld = rm.ld
        AND le = rm.le
        AND lf = rm.lf
        AND code = rm.code
        AND lcode = rm.lcode
        and user_id = username;

My problem is that this rarely updating because rm.lf and rm.le have NULL values in the database. How can I combat this, and create the correct update.

Upvotes: 0

Views: 2120

Answers (3)

niceApp
niceApp

Reputation: 3153

update work 
        set kilometers = calc_kilo, 
            kilo_test = test_kilo                                   
        where lc = rm.lc 
        AND ld = rm.ld 
        AND NVL(le,'x') = NVL(rm.le,'x') 
        AND NVL(lf,'x') = NVL(rm.lf,'x') 
        AND code = rm.code 
        AND lcode = rm.lcode 
        and user_id = username;

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52386

I have a cursor which grabs a bunch of records with multiple fields. I then run two separate SELECT statements in a LOOP from the cursor results to grab some distances and calculate those distances. These work perfectly.

When I go to update the table with the new values, my problem is that there are four pieces of specific criteria.

The first thing I'd look at is not using a cursor to read data, then make calculations, then perform updates. In 99% of cases it's faster and easier to just run updates that do all of this in a single step

Upvotes: 1

Welbog
Welbog

Reputation: 60438

If I'm understanding you correctly, you want to match lf with rm.lf, including when they're both null? If that's what you want, then this will do it:

...
AND (lf = rm.lf 
  OR (lf IS NULL AND rm.lf IS NULL)
)
...

It's comparing the values of lf and rm.lf, which will return false if either is null, so the OR condition returns true if they're both null.

Upvotes: 2

Related Questions