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

Question

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.

Answer 1

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.

Answer 2

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