You Asked

This code works, but I have to repeat the select statement with different column about a dozen updates in itemized table.
Is there a way to list all the set fields and equate them to the SELECT fields.
I can do them 1 at a time...for dozen, but if I had 100, that would not be so efficient.
Any thoughts? Thanks so much!

Joseph Giallombardo

Code:

UPDATE DOBS.CP_ITEMIZED
SET DOBS.CP_ITEMIZED.PHYSICIAN_FIRST_NAME =
(
SELECT P.First_Name_1
FROM DOBS.CP_FED_HCP_FOR_RPT H
INNER JOIN DOBS.CP_NPI_TAKEDAID_MATCH M ON H.TAKEDA_ID = M.TAKEDA_ID
INNER JOIN DOBS.CP_CMS_PHYSICIAN_LIST P ON H.DCH_NPI = P.NPI
WHERE DOBS.CP_ITEMIZED.TAKEDA_ID = H.TAKEDA_ID
AND H.In_CMS_list = 'Yes'
AND H.Valid_For_Federal = 'Yes'
AND H.In_NPI_Match = 'Yes'
),
DOBS.CP_ITEMIZED.PHYSICIAN_MIDDLE_NAME =
(
SELECT P.Middle_Name_1
FROM DOBS.CP_FED_HCP_FOR_RPT H
INNER JOIN DOBS.CP_NPI_TAKEDAID_MATCH M ON H.TAKEDA_ID = M.TAKEDA_ID
INNER JOIN DOBS.CP_CMS_PHYSICIAN_LIST P ON H.DCH_NPI = P.NPI
WHERE DOBS.CP_ITEMIZED.TAKEDA_ID = H.TAKEDA_ID
AND H.In_CMS_list = 'Yes'
AND H.Valid_For_Federal = 'Yes'
AND H.In_NPI_Match = 'Yes'
)
WHERE EXISTS
(
SELECT 1
FROM DOBS.CP_FED_HCP_FOR_RPT
WHERE DOBS.CP_FED_HCP_FOR_RPT.TAKEDA_ID = DOBS.CP_ITEMIZED.TAKEDA_ID
);

and Connor said...

Yes, as long as the criteria is the same, you can do it all in a single sub-select, and it will most likely be more efficient as well

SQL> create table t
  2  as select * from user_objects;

Table created.

SQL>
SQL> create table t1
  2  as select * from t;

Table created.

SQL>
SQL> set autotrace on
SQL> update t
  2  set
  3    created = ( select created from t1 where object_id = t.object_id ),
  4    object_name = ( select object_name from t1 where object_id = t.object_id ),
  5    object_type = ( select object_type from t1 where object_id = t.object_id )
  6  /

449 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 786734310

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |   449 | 16164 |  8087  (17)| 00:00:01 |
|   1 |  UPDATE            | T    |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T    |   449 | 16164 |     5   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    13 |     5   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| T1   |     1 |    19 |     5   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL| T1   |     1 |    14 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("OBJECT_ID"=:B1)
   4 - filter("OBJECT_ID"=:B1)
   5 - filter("OBJECT_ID"=:B1)


Statistics
----------------------------------------------------------
         12  recursive calls
       4950  db block gets
      22732  consistent gets
          8  physical reads
     249960  redo size
        864  bytes sent via SQL*Net to client
       1158  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        449  rows processed

SQL>
SQL> update t
  2  set (created,object_name,object_type) =
  3     ( select created,object_name,object_type from t1 where object_id = t.object_id )
  4  /

449 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 4073572437

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |   449 | 16164 |  8087  (17)| 00:00:01 |
|   1 |  UPDATE            | T    |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T    |   449 | 16164 |     5   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    36 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("OBJECT_ID"=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
       2250  db block gets
       7582  consistent gets
          0  physical reads
     150564  redo size
        865  bytes sent via SQL*Net to client
       1053  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        449  rows processed

Also, check out the MERGE command which often is a good choice here as well, eg

SQL> merge into t
  2  using ( select * from t1 ) t1
  3  on ( t.object_id = t1.object_id )
  4  when matched then update
  5  set
  6    t.created = t1.created,
  7    t.object_name = t1.object_name,
  8    t.object_type = t1.object_type;

444 rows merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 2689172021

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |      |   444 | 52836 |    10   (0)| 00:00:01 |
|   1 |  MERGE               | T    |       |       |            |          |
|   2 |   VIEW               |      |       |       |            |          |
|*  3 |    HASH JOIN         |      |   444 | 94128 |    10   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   449 | 47594 |     5   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T    |   449 | 47594 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")


Statistics
----------------------------------------------------------
         73  recursive calls
        570  db block gets
        110  consistent gets
          0  physical reads
     169808  redo size
        865  bytes sent via SQL*Net to client
       1119  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        444  rows processed

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Questions about "what you said".

Joseph Giallombardo, August 01, 2017 - 2:43 pm UTC

Can I get a followup response?

What is "user_objects" in the first SQL> .....as user_objects

And, I am unclear on 3 statement the object_id, object_name, object_type...???

created = ( select created from t1 where object_id = t.object_id ),
4 object_name = ( select object_name from t1 where object_id = t.object_id ),
5 object_type = ( select object_type from t1 where object_id = t.object_id )

August 01, 2017 - 4:00 pm UTC

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536055900346482366

Issue resolved

Joseph Giallombardo, August 01, 2017 - 7:18 pm UTC

Thanks for the help, I switch over to the Merge and also found the concept (new to me) of CTE
I put my complex query in the CTE and then used it with the merge. I also realized that I was missing some fields in the query, but the cte reduced having to repeat.

I am learning a lot...thanks.

MERGE INTO DOBS.CP_ITEMIZED target_table
USING
(WITH cte AS
(
SELECT H.TAKEDA_ID, P.First_Name_1, P.Middle_Name_1, P.Last_Name_1, P.Suffix_1, M.PRACTICE_LOC_ADDRESS, M.PRACTICE_LOC_CITY,
M.PRACTICE_LOC_STATE, M.PRACTICE_LOC_ZIP, M.PRACTICE_LOC_COUNTRY, H.DCH_NPI, M.TAXONOMY_1, P.LCNS_1, P.LCNS_2,P.STATE_1, P.STATE_2
FROM DOBS.CP_FED_HCP_FOR_RPT H
INNER JOIN DOBS.CP_NPI_TAKEDAID_MATCH M ON H.TAKEDA_ID = M.TAKEDA_ID
INNER JOIN DOBS.CP_CMS_PHYSICIAN_LIST P ON H.DCH_NPI = P.NPI
WHERE H.In_CMS_list = 'Yes'
AND H.Valid_For_Federal = 'Yes'
AND H.In_NPI_Match = 'Yes'
)
SELECT * FROM cte
) source_table
ON (target_table.TAKEDA_ID = source_table.TAKEDA_ID)
WHEN MATCHED THEN
UPDATE SET
target_table.PHYSICIAN_FIRST_NAME = source_table.First_Name_1,
target_table.PHYSICIAN_MIDDLE_NAME = source_table.Middle_Name_1,
target_table.PHYSICIAN_LAST_NAME = source_table.Last_Name_1,
target_table.PHYSICIAN_NAME_SUFFIX = source_table.Suffix_1,
target_table.RECIPIENT_PR_BUS_ST_ADDR_1 = source_table.PRACTICE_LOC_ADDRESS,
target_table.RECIPIENT_CITY = source_table.PRACTICE_LOC_CITY,
target_table.RECIPIENT_STATE = source_table.PRACTICE_LOC_STATE,
target_table.RECIPIENT_ZIP_CODE = source_table.PRACTICE_LOC_ZIP,
target_table.RECIPIENT_COUNTRY = source_table.PRACTICE_LOC_COUNTRY,
target_table.PHYS_OR_TEACH_HOSP_NPI = source_table.DCH_NPI,
target_table.PHYSICIAN_SPECIALTY = source_table.TAXONOMY_1,
target_table.PHYSICIAN_DATA_SOURCE_PRIMARY = 'CMS',
target_table.PHYS_LIC_STATE_LIC_NO_1 =
CASE
when Length(source_table.LCNS_1)<18 And source_table.LCNS_1<>'000000000'
then source_table.STATE_1 || '-' || source_table.LCNS_1
else source_table.STATE_2 || '-' || source_table.LCNS_2
END

August 03, 2017 - 1:13 am UTC

nice work.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Questions about "what you said".

Issue resolved

More to Explore

PL/SQL demos

PL/SQL docs

UPDATE CODE REFINEMENT

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Questions about "what you said".

Issue resolved

More to Explore

PL/SQL demos

PL/SQL docs