0

I have a table with this structure

Table1(Cust_No    Varchar2(50),
       First_Name Varchar2(50),
       Last_Name  Varchar2(50),
       Error_Code Number(1)   ,
       Error_Desc Varchar2(50))

there is another table with this structure

Table2(Cust_No            Varchar2(50),
       Customer_FirstName Varchar2(50),
       Customer_LastName  Varchar2(50)
       )

Imagine that Column Cust_No from Table1 has data.By this I mean there are some data for Cust_No in this field. I need to Update other columns of the table based on this column and column Cust_No of Table2 and find First_Name and Last_Name for each Customer Number. What I have written so far is this :

-- FIND CUSTOMER'S FIRST AND LAST NAME

   MERGE INTO (SELECT * FROM REQUEST R WHERE R.IS_CHECKED IS NULL) R
   USING VMI_DIMCUSTOMER V
   ON (V.CUSTOMER_NUM = R.CUST_NO)
    WHEN MATCHED THEN
    UPDATE
      SET R.FIRST_NAME = V.CUST_FIRST_NAME,
          R.LAST_NAME  = V.CUST_LAST_NAME,
          R.ERROR_CODE = 0,
          R.IS_CHECKED = 1;

            COMMIT;

   -- CUSTOMERS NOT FOUND


  UPDATE /*+ PARALLEL(4) */ REQUEST T
     SET T.FIRST_NAME = '--',
         T.LAST_NAME  = '--',
         T.ERROR_CODE = 2,
         T.ERROR_DESC = 'Customer Not Found',
         T.IS_CHECKED = 0
      WHERE T.FIRST_NAME IS NULL;

        COMMIT;

What I'm doing in this query is that First of all, I'm using a Merge statement to find customer's first and last name and update column Is_Checked to 1 when Customers have first and last name. In the second part I'm updating columns that does not match to any records in Table2.

I want to know wether there are better ways to write this query in terms of performance.

1
  • 1
    Is the second statement supposed to work over all records rather than just those where is_checked is null?
    – Boneist
    Commented Sep 9, 2019 at 10:15

2 Answers 2

1

If I understand what you're doing there (which I may not be 100% .. ) .. it appears you're trying to update REQUEST table, using the other VMI_DIMCUSTOMER table as "input" .. but only if records exist. If there are not records in VMI_DIMCUSTOMER table, for a given record in REQUEST, you want to flag the record in REQUEST with your "customer not found" message, and set first/last names to '--' and error code to 2 and is_checked to 0.

If you find the record, instead the names are populated, is_checked is set to 1, and error code is set to 0.

This can be done in a single sql .. I've used this method before. The basic idea is, forget the update, merge, etc .. and just write a SELECT statement that returns the END STATE that you really want.

If I understood you properly . you want something like this:

  SELECT CASE WHEN v.cust_first_name IS NULL
              THEN '--' ELSE r.first_name END   first_name,
         CASE WHEN v.cust_first_name IS NULL
              THEN '--' ELSE r.last_name END    last_name,
         CASE WHEN v.cust_first_name IS NULL
              THEN 2 ELSE 0 END                 error_code,
         CASE WHEN v.cust_first_name IS NULL
              THEN 0 ELSE 1 END                 is_checked,
         CASE WHEN v.cust_first_name IS NULL
              THEN 'Customer Not Found' ELSE NULL  END     error_desc
    FROM request           r
        LEFT OUTER JOIN
           vmi_dimcustomer   v
        ON v.cust_first_name = r.first_name
        and v.cust_last_name = r.last_name
   WHERE r.error_code = 0
     AND r.is_checked = 1
  /

By doing an outer join, we'll include ALL rows in REQUEST, even if we don't find them in VMI_DIMCUSTOMER. we check if the CUST_FIRST_NAME in VMI_DIMCUSTOMER is null (you can instead check for a CUST_NO column if you have one .. but you showed the table structure for something different than what you're using .. so wasn't sure).

And use CASE to pull/set whatever value you want/need for each row, depending on if you find it in VMI_DIMCUSTOMER or not.

Once you have that "final result set" query ironed out (and it's easy to test run to verify .. ;) )

Plugging it into a merge is simple ... first, add rowid to the query to make it easier to pick out the row .. and then plug into a simple MERGE :

  MERGE INTO request  old
     USING (
           SELECT r.rowid  rid,
                  CASE WHEN v.cust_first_name IS NULL
                       THEN '--' ELSE r.first_name END   first_name,
                  CASE WHEN v.cust_first_name IS NULL
                       THEN '--' ELSE r.last_name END    last_name,
                  CASE WHEN v.cust_first_name IS NULL
                       THEN 2 ELSE 0 END                 error_code,
                  CASE WHEN v.cust_first_name IS NULL
                       THEN 0 ELSE 1 END                 is_checked,
                  CASE WHEN v.cust_first_name IS NULL
                       THEN 'Customer Not Found' ELSE NULL  END     error_desc
             FROM request           r
                 LEFT OUTER JOIN
                    vmi_dimcustomer   v
                 ON v.cust_first_name = r.first_name
                 and v.cust_last_name = r.last_name
            WHERE r.error_code = 0
              AND r.is_checked = 1
           )  new
     ON ( new.rid = old.rowid )
     WHEN MATCHED THEN
        UPDATE
           SET old.first_name = new.first_name,
               old.last_name = new.last_name,
               old.error_code = new.error_code,
               old.is_checked = new.is_checked,
               old.error_desc = new.error_desc
  /

viola .. 1 sql, and it'll set the flags depending on if it found them in the other table or not.. ;)

If it doesn't quite work as you need, please clarify in more detail your requirements .. it shouldn't be hard to modify the merge to fit your needs.

1

Assuming your second update needs to only work on the records that were just updated in the merge statement, the following should do the trick:

MERGE INTO request tgt
  USING vmi_dimcustomer src
    ON (tgt.cust_no = src.customer_num AND tgt.is_checked IS NULL)
WHEN MATCHED THEN
  UPDATE SET tgt.first_name = CASE WHEN src.cust_first_name IS NULL THEN '--' ELSE src.cust_first_name END,
             tgt.last_name = CASE WHEN src.cust_first_name IS NULL THEN '--' ELSE src.cust_last_name END,
             tgt.error_code = CASE WHEN src.cust_first_name IS NULL THEN 2 ELSE 0 END,
             tgt.error_desc = CASE WHEN src.cust_first_name IS NULL THEN 'Customer Not Found' ELSE NULL END,
             tgt.is_checked = CASE WHEN src.cust_first_name IS NULL THEN 0 ELSE 1 END;

COMMIT;

This works by checking the results of the join between the source and target tables and using the relevant values for the columns being updated, depending on whether the first_name column in vmi_dimcustomer is null or not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.