UPDATE with EXISTS

Question

I'm testing this UPDATE statement to update all 4%, 8%, and 9% parts in our database. I'm trying to get the QTY_MULTIPLE value to match the CASES per layer value.

So, the REGEXP_LIKE, will eventually read:

> Regexp_like ( sp.part_no, '^4|^8|^9' )

It doesn't right now because I'm testing three specific parts. I want to make sure the rest of the statement works the way that it's supposed to.

Here's what I'm testing with:

UPDATE SALES_PART_TAB sp
SET    sp.qty_multiple = ( SELECT cases_per_layer
                           FROM   HH_INV_PART_CHARS
                           WHERE  sp.part_no = HH_INV_PART_CHARS.part_no AND
                                  sp.contract = HH_INV_PART_CHARS.contract )
WHERE  Regexp_like ( sp.part_no, '^410-0017|^816-0210|^921-0003' ) AND
       EXISTS
       ( SELECT sp.contract,
       sp.part_no,
       sp.qty_multiple,
       HH_INV_PART_CHARS.cases_per_layer
         FROM   SALES_PART sp
                inner join HH_INV_PART_CHARS
                        ON sp.part_no = HH_INV_PART_CHARS.part_no AND
                           sp.contract = HH_INV_PART_CHARS.contract
         WHERE  sp.qty_multiple != HH_INV_PART_CHARS.cases_per_layer ); 

When I run this statement, it updates 16 rows.

However, I'm expecting it to update 15 rows. I reached this conclusion by running the following SELECT statement:

SELECT sp.contract,
       sp.catalog_no,
       sp.qty_multiple,
       HH_INV_PART_CHARS.cases_per_layer
FROM   SALES_PART sp
       inner join HH_INV_PART_CHARS
               ON sp.part_no = HH_INV_PART_CHARS.part_no AND
                  sp.contract = HH_INV_PART_CHARS.contract
WHERE  sp.qty_multiple != HH_INV_PART_CHARS.cases_per_layer AND
       Regexp_like ( sp.part_no, '^410-0017|^816-0210|^921-0003' )

I think the problem I'm having is the UPDATE statement is updating all rows where the part_no and contract from the sales_part table exist on HH_INV_PART_CHARS. It's not limiting the update to part where the qty_multiple isn't equal to the cases_per_layer (which is what I want).

I'm a little stumped right now. I've been trying to work on both the subqueries but I'm not having any luck identifying where the problem is.

Answer 1

The Regexp_like ( sp.part_no,...) in the update query refers to SALES_PART_TAB.spart_no, while in the second query it refers to SALES_PART.spart_no.

One of the causes of the fog is that you redefine the alias sp in the same query, and so the exists subquery does not relate in any way to the record that is being updated. This means that if you would throw away the exists condition, you would still update 16 records. It seems very unlikely that this is what you want.

Use a different alias, so you can distinguish which table you want to refer to.