Home » SQL & PL/SQL » SQL & PL/SQL » how to combine all fields in one update statement (oracle 10g)
how to combine all fields in one update statement [message #442585] Tue, 09 February 2010 04:00 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

UPDATE GT_TEST SPT 
       SET SPT.PARTY_NAME = 
           (SELECT BP.PARTY_NAME 
              FROM PARTY BP 
             WHERE BP.CODE = SPT.PARTY_ID) 
             WHERE SPT.PARTY_ID IS NOT NULL; 
     
    UPDATE GT_TEST SPT 
       SET SPT.AGENTNAME = 
           (SELECT BP.PARTY_NAME 
              FROM PARTY BP 
             WHERE BP.CODE = SPT.AGENTCODE) 
    WHERE SPT.AGENTCODE is NOT NULL; 
     
    UPDATE GT_TEST SPT 
       SET SPT.Customername = 
           (SELECT BP.PARTY_NAME 
              FROM PARTY BP 
             WHERE BP.CODE = SPT.Customer) 
             WHERE  SPT.Shipper IS NOT NULL ; 
     
     
TO EXECUTE it faster I have modified the above query TO the below one but am NOT getting expected results.
what IS the wrong WITH the below query? 
 
 
    UPDATE GT_TEST SPT 
       SET (PARTY_NAME, AGENTNAME, Customername) = 
       ( 
       SELECT BP.PARTY_NAME,  
       BP_AGENT.PARTY_NAME,  
       BP_Shipper.PARTY_NAME 
           FROM PARTY BP, 
                PARTY BP_AGENT, 
                PARTY BP_Customer 
          WHERE BP.CODE = 
                SPT.PARTY_ID 
            AND BP_AGENT.CODE = 
                SPT.AGENTCODE 
            AND BP_Customer.CODE = 
                SPT.Customer);

[Updated on: Tue, 09 February 2010 06:11] by Moderator

Report message to a moderator

Re: how to combine all fields in one update statement [message #442590 is a reply to message #442585] Tue, 09 February 2010 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Telling us how the result differs from what you expect would help.
I assume party.code is unique.
Your original updates have additional where clauses that check if the field you're trying to update is null.
Re: how to combine all fields in one update statement [message #442594 is a reply to message #442585] Tue, 09 February 2010 05:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The basic issue with your updates is that each update is for a different set of rows. Thus the question becomes how does one combine multiple updates that affect different rows on the same tables.

The answer is that you must execute a single update which operates on the superset of all rows from all your individual updates but which can discriminate at the column level, whether to change a column or not.

Because this is a somewhat advanced technique, I will offer up a solution rather than trying to push you towards one on your own. However I will explain why it works so that you can on your own do it next time.

Here are your original three updates where I have properly indented the outer WHERE clause to reenforce the set of rows each update is operating on. Pleast notice that each update affects a different set of rows.

UPDATE GT_TEST SPT 
       SET SPT.PARTY_NAME = 
           (SELECT BP.PARTY_NAME 
              FROM PARTY BP 
             WHERE BP.CODE = SPT.PARTY_ID) 
WHERE SPT.PARTY_ID IS NOT NULL; 


UPDATE GT_TEST SPT 
       SET SPT.AGENTNAME = 
           (SELECT BP.PARTY_NAME 
              FROM PARTY BP 
             WHERE BP.CODE = SPT.AGENTCODE) 
WHERE SPT.AGENTCODE is NOT NULL; 


    UPDATE GT_TEST SPT 
       SET SPT.Customername = 
           (SELECT BP.PARTY_NAME 
              FROM PARTY BP 
             WHERE BP.CODE = SPT.Customer) 
WHERE  SPT.Shipper IS NOT NULL ; 


First we create an update shell that will process the superset of rows from our original updates. Notice how the WHERE clause of this update gets all rows from all three updates. Your AND version did not do this. Your version took the intersection of rows. We want the union of rows. Thus we use OR not AND.

update gt_test spt set
where (
         shipper is not null or
         agenctcode is not null or
         party_id is not null
      )
/


Now we return the column set clauses so that each column will get its data and be updated. However, take care, we are not done yet. We need to make sure that each correlated subquery only returns data for the set of rows it was originally intended to return data for. We must therefor add the relevant part of the WHERE clause component to each subquery's WHERE clause. In this case this is redundant because of the nature of the tests but as a general translation process of going from multiple updates to one, this is the process to follow.

update gt_test spt set
        party_name = (
                        select bp.party_name
                        from party bp
                        where bp.code = spt.party_id
                     )
      , agentname = (
                       select bp.party_name
                       from party bp
                       where bp.code = spt.agencycode
                    )
      , customername = (
                          select bp.party_name
                          from party bp
                          where bp.code = spt.customer
                       )
where (
        shipper is not null or
        agenctcode is not null or
        party_id is not null
      )
/


So here we see the subquerys where each is restricted to its original rows. But we are still not done. By returning the subquerys to be affective on the set of rows they were originally intended for, we have created a situation when each subquery can return null. This would result in setting the corresponding name to null and we do not want that. So we have to modify our subquery to ensure that if a null is returned, we update the column to its current value and thus are not changing it.

update gt_test spt set
        party_name = (
                        select bp.party_name
                        from party bp
                        where bp.code = spt.party_id
                        and spt.party_id is not null
                     )
      , agentname = (
                       select bp.party_name
                       from party bp
                       where bp.code = spt.agencycode
                       and spt.agencycode is not null
                    )
      , customername = (
                          select bp.party_name
                          from party bp
                          where bp.code = spt.customer
                          and spt.shipper is not null
                       )
where (
        shipper is not null or
        agenctcode is not null or
        party_id is not null
      )
/


Here we see the final update. Notice the user of NVL. This ensures that if the underlying subquery does not find any rows then the value in the updated field remains unchanged.

update gt_test spt set
        party_name = nvl(
                          (
                             select bp.party_name
                             from party bp
                             where bp.code = spt.party_id
                             and spt.party_id is not null
                          )
                         , spt.party_name
                        )
      , agentname = nvl(
                         (
                            select bp.party_name
                            from party bp
                            where bp.code = spt.agencycode
                            and spt.agencycode is not null
                         )
                        , spt.agentname
                       )
      , customername = nvl(
                            (
                               select bp.party_name
                               from party bp
                               where bp.code = spt.customer
                               and spt.shipper is not null
                            )
                           , spt.customername
                          )
where (
        shipper is not null or
        agenctcode is not null or
        party_id is not null
      )
/


As a side affect, you should consider that this technique only works when NULL is not a legitemate value for each of our columns. Thus in your case this combining of updates only makes sense if you can never set any of your name fields to null. In your case this appears to be true with the exception of the third update. I question the validity of your third update. Your Outer WHERE clause does not follow the pattern of the other two. Please check it.

To the process was this.

Quote:
1) create a shell update that goes after the superset of rows from all your updates.

2) add the column update logic.

3) if the column update logic is a subquery, then put in additional logic to ensure the subquery only operates on the original set of rows it was intended for.

4) use NVL to make sure that if null is returned by a subquery, you substitute the current column value so that columns are not accidentaly set to null.

5) evaluate the logic of updating each column. You must be certain that NULL is not a valid value, or at least not valid for each of your updates.

So one might ask why bother with this. Well the reason is one of performance. Normally in this situation, the original updates are scanning a large table. Thus three updates means three tables scans. One update means only one table scan. In general terms we say that we are replacing multiple passes of the data with only one pass of the data. Thus we can extend th concept to cover any set of updates that is taking a long time ragardless of the query plans of the updates. If each of these updates is a long running update then significant performance gains can be had by combining them into one update.

You must test your new single update to ensure you are getting the proper updates and that infact it was worth the effort performance wise to have bothered. Otherwise you should stick with the original updates because they are clearer about what they are doing.

Good luck, Kevin
Re: how to combine all fields in one update statement [message #442600 is a reply to message #442594] Tue, 09 February 2010 05:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Notwithstanding Kevin's monumentally detailed answer, I think you are still going to have problems with this UPDATE because the SQL is the SET clause is NESTED. That is: it runs once for every row returned in the outer query.

A more efficient way is to run a larger query once only and then apply the result set to your table.

MERGE
INTO  GT_TEST
USING (
       SELECT gt_test.rowid rid
              BP.PARTY_NAME,  
              BP_AGENT.PARTY_NAME AS AGENTNAME,  
              BP_Customer.PARTY_NAME  AS CUSTOMERNAME
       FROM   GT_TEST SPT
       LEFT JOIN PARTY BP ON BP.CODE = SPT.PARTY_ID
       LEFT JOIN PARTY BP_AGENT ON BP_AGENT.CODE = SPT.AGENTCODE
       LEFT JOIN PARTY BP_Customer ON BP.CODE = SPT.Customer
) new
ON (new.rid = gt_test.rowid)
WHEN MATCHED THEN SET
    GT_TEST.PARTY_NAME = NVL(new.PARTY_NAME, GT_TEST.PARTY_NAME)
,   GT_TEST.AGENTNAME = NVL(new.AGENTNAME, GT_TEST.AGENTNAME)
,   GT_TEST.CUSTOMERNAME = NVL(new.CUSTOMERNAME, GT_TEST.CUSTOMERNAME)


See this article for more information.

Ross Leishman
Re: how to combine all fields in one update statement [message #442604 is a reply to message #442594] Tue, 09 February 2010 06:18 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
One small quibble with that analysis:

Kevin Meade wrote on Tue, 09 February 2010 11:30

First we create an update shell that will process the superset of rows from our original updates. Notice how the WHERE clause of this update gets all rows from all three updates. Your AND version did not do this. Your version took the intersection of rows. We want the union of rows. Thus we use OR not AND.


No. OPs modified update updates all rows, not an intersection. The only where clause is in the subquery, which is basically a cartesian join of the three original sub-queries.
This isn't an issue in itself since we can assume each sub-query can only return one row, but the ANDs will cause a problem if one of the three sub-queries doesn't find a match as that'll cause all three columns to be updated to null.
And that'll happen for any row in gt_test where one of the columns is null.

I do basically agree with everything you said, just thought that bit was potentially misleading as to what the problem was.


I also think that the final update would be clearer in intent with the use of case statements:
UPDATE gt_test spt 
SET party_name = (CASE WHEN spt.party_id IS NOT NULL 
                       THEN (
                             select bp.party_name
                             from party bp
                             where bp.code = spt.party_id
                            )
                       ELSE spt.party_name
                  END
                 ),
    agentname = (CASE WHEN spt.agentcode IS NOT NULL
                      THEN (
                            select bp.party_name
                            from party bp
                            where bp.code = spt.agentcode
                           )
                      ELSE spt.agentname
                 END
                ),
    customername = (CASE WHEN spt.shipper is not NULL
                         THEN (
                               select bp.party_name
                               from party bp
                               where bp.code = spt.shipper
                              )
                         ELSE spt.customername
                    END
                   )
where (
        shipper IS NOT NULL OR
        agentcode IS NOT NULL OR
        party_id IS NOT NULL
      );


You may or may not what to add an nvl round each sub-query but there was no check in the original updates to ensure the sub-queries found rows.
If shipper, agentcode and party_id are foreign keyed to the party table then an nvl will be unnecessary.
Re: how to combine all fields in one update statement [message #442606 is a reply to message #442585] Tue, 09 February 2010 06:35 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
this is another case but i am getting missing keyword error.
any wrong with this update statment.

MERGE INTO GT_TEST gt_test
USING (SELECT gt_test.rowid rid,
              SPT.ACCRCODE,
              spt.ACCRNAME,
              SPT.ACCTCODE,
              spt.acctname
         FROM GT_TEST SPT
         LEFT JOIN CHARGEMASTER CMAP  ON CMAP.CHARGEID = SPT.CHARGE_CODE
         LEFT JOIN ACCTMASTER ACM  ON ACM.ACCTCODE = CMAP.ACCR_ACCTCODE

         LEFT JOIN CHARGEMASTER CMAP1 ON CMAP1.CHARGEID = SPT.CHARGE_CODE
         LEFT JOIN ACCTMASTER ACM1 ON ACM1.ACCTCODE = CMAP1.ACT_ACCTCODE) new
ON (new.rid = gt_test.rowid)
WHEN MATCHED THEN
     SET GT_TEST.ACCRCODE = NVL(new.ACCRCODE,  GT_TEST.ACCRCODE),
         GT_TEST.ACCRNAME = NVL(new.ACCRNAME,GT_TEST.ACCRNAME),
         GT_TEST.ACCTCODE = NVL(new.ACCTCODE,GT_TEST.ACCTCODE),
         GT_TEST.ACCTNAME = NVL(NEW.ACCTNAME, GT_TEST.ACCTNAME);
Re: how to combine all fields in one update statement [message #442608 is a reply to message #442585] Tue, 09 February 2010 06:41 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
corrected the query.
Re: how to combine all fields in one update statement [message #442670 is a reply to message #442608] Tue, 09 February 2010 20:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So do you still have a problem?

Ross Leishman
Re: how to combine all fields in one update statement [message #442689 is a reply to message #442585] Wed, 10 February 2010 00:09 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Thanks for detailed explaination by kevin,leisman and offcourcse michel and other colleagues.i cannot forget your support you are giving.orafaq rocks...thanks once again for giving wonderful support.
Re: how to combine all fields in one update statement [message #442697 is a reply to message #442689] Wed, 10 February 2010 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and offcourcse michel

Yeah, I was really off course as I didn't participate to this topic.

Regards
Michel
Re: how to combine all fields in one update statement [message #442704 is a reply to message #442697] Wed, 10 February 2010 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't?!? ./fa/450/0/ How could that happen? ./fa/1587/0/
Re: how to combine all fields in one update statement [message #442735 is a reply to message #442585] Wed, 10 February 2010 05:15 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Michel Cadot
Messages: 30808

We just assume that your are on every thread everywhere.
Previous Topic: ORA-06508: PL/SQL: could not find program unit being called
Next Topic: Count
Goto Forum:
  


Current Time: Sat Dec 03 16:05:26 CST 2016

Total time taken to generate the page: 0.06174 seconds