how to combine all fields in one update statement [message #442585] |
Tue, 09 February 2010 04:00  |
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 #442594 is a reply to message #442585] |
Tue, 09 February 2010 05:30   |
 |
Kevin Meade
Messages: 2103 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   |
rleishman
Messages: 3728 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   |
cookiemonster
Messages: 13965 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   |
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);
|
|
|
|
|
|
|
|
|