Prev: Retive username / machine name
Next: What instances on this box?
From: Satya on 17 Jul 2008 03:16
Hello Everybody,

I am writing a stored proc for generating a report and it has below SQLs.

I am attempting to update the Premium and remittance basing on the source
id. But its not giving me the desired result. I am trying to update 34
columns basing on the source id.

Could anyone please tell me why Premium and Remittance columns are not
getting updated and what is the best way to achieve it?

I am working on SQL 2000 SP4

Below it the code snippet from the proc. (abridged)

declare @Remittances table
(
OrderId int,
SourceId int,
Premium money,
Remittance money
)


declare @Result table
(
OrderId int,
OwnerPremium money,
OwnerRemittance money,
LenderPremium money,
LenderRemittance money,
BrokerPremium money,
BrokerRemittance money,
ClsAgentPremium money,
ClsAgentRemittance money,
UWPremium money,
UWRemittance money
)

Insert into @Result (OrderId) values (123)


insert into @Remittances values (123, 7, 500, 50)
insert into @Remittances values (123, 8, 250, 22)
insert into @Remittances values (123, 9, 300, 30)
insert into @Remittances values (123, 10, 200, 15)



update R
set OwnerPremium = case T.SourceId When 7 then T.Premium else OwnerPremium
end,
OwnerRemittance = case T.SourceId When 7 then T.Remittance else
OwnerRemittance end,
LenderPremium = case T.SourceId When 8 then T.Premium else LenderPremium end,
LenderRemittance = case T.SourceId When 8 then T.Remittance else
LenderRemittance end,
BrokerPremium = case T.SourceId When 9 then T.Premium else BrokerPremium end,
BrokerRemittance = case T.SourceId When 9 then T.Remittance else
BrokerRemittance end,
ClsAgentPremium = case T.SourceId When 10 then T.Premium else
ClsAgentPremium end,
ClsAgentRemittance = case T.SourceId When 10 then T.Remittance else
ClsAgentRemittance end,
UWPremium = case T.SourceId When 14 then T.Premium else UWPremium end,
UWRemittance = case T.SourceId When 14 then T.Remittance else UWRemittance
end
from @Result R inner join @Remittances T on R.OrderId = T.OrderId


select *
from @Result


T
--
Satya
From: ML on 17 Jul 2008 03:57
The UPDATE...FROM syntax can have unpredictable results unless the
relationship between the tables referenced in the from clause is one-to-one.

Add this to your query:

select *
from @Result R
inner join @Remittances T
on R.OrderId = T.OrderId

See?


Now give me another minute. :)


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
From: ML on 17 Jul 2008 04:04
Try this:

update R
set R.OwnerPremium
= coalesce((
select T.Premium
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 7)
), R.OwnerPremium)
,R.OwnerRemittance
= coalesce((
select T.Remittance
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 7)
), R.OwnerRemittance)
,R.LenderPremium
= coalesce((
select T.Premium
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 8)
), R.LenderPremium)
,R.LenderRemittance
= coalesce((
select T.Remittance
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 8)
), R.LenderRemittance)
,R.BrokerPremium
= coalesce((
select T.Premium
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 9)
), R.BrokerPremium)
,R.BrokerRemittance
= coalesce((
select T.Remittance
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 9)
), R.BrokerRemittance)
,R.ClsAgentPremium
= coalesce((
select T.Premium
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 10)
), R.ClsAgentPremium)
,R.ClsAgentRemittance
= coalesce((
select T.Remittance
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 10)
), R.ClsAgentRemittance)
,R.UWPremium
= coalesce((
select T.Premium
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 14)
), R.UWPremium)
,R.UWRemittance
= coalesce((
select T.Remittance
from @Remittances T
where (T.OrderID = R.OrderID)
and (T.SourceID = 14)
), R.UWRemittance)
from @Result R

Note that the FROM clause is used in this UPDATE statement only to provide a
way to create an alias for the modified table.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
From: Satya on 17 Jul 2008 04:31
Hi Matija, the query helped, thanks a lot.
But I am not still convinced on why SQL is not able to evaluate update with
case statements if the relationship is 1-to-n. What anamoly it will face for
this scenario?

Thanks a lot again
- Satya
--
Satya
"ML" wrote:

> Try this:
>
> update R
> set R.OwnerPremium
> = coalesce((
> select T.Premium
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 7)
> ), R.OwnerPremium)
> ,R.OwnerRemittance
> = coalesce((
> select T.Remittance
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 7)
> ), R.OwnerRemittance)
> ,R.LenderPremium
> = coalesce((
> select T.Premium
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 8)
> ), R.LenderPremium)
> ,R.LenderRemittance
> = coalesce((
> select T.Remittance
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 8)
> ), R.LenderRemittance)
> ,R.BrokerPremium
> = coalesce((
> select T.Premium
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 9)
> ), R.BrokerPremium)
> ,R.BrokerRemittance
> = coalesce((
> select T.Remittance
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 9)
> ), R.BrokerRemittance)
> ,R.ClsAgentPremium
> = coalesce((
> select T.Premium
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 10)
> ), R.ClsAgentPremium)
> ,R.ClsAgentRemittance
> = coalesce((
> select T.Remittance
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 10)
> ), R.ClsAgentRemittance)
> ,R.UWPremium
> = coalesce((
> select T.Premium
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 14)
> ), R.UWPremium)
> ,R.UWRemittance
> = coalesce((
> select T.Remittance
> from @Remittances T
> where (T.OrderID = R.OrderID)
> and (T.SourceID = 14)
> ), R.UWRemittance)
> from @Result R
>
> Note that the FROM clause is used in this UPDATE statement only to provide a
> way to create an alias for the modified table.
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
From: ML on 17 Jul 2008 04:56
The UPDATE...FROM syntax is not ANSI, but proprietary to SQL Server.
Generally, you are advised against using it unless you fully understand its
limitations.

For each row in the modified table one row from the source set is used as
the source of modified data. This means that each row of the destination
table *may be* modified as many times as there are corresponding rows in the
source set, the final state of the row being the result of the "last" update.

As the order in which rows are being processed is unpredictable, it is also
unpredictable which row will be processed last, therefore it is unpredictable
what data will be in the destination when the modification is done.

IOW: when updating each row of the destination table only one row from the
source set is accessible to the optimizer at any given time, therefore all
the combinations cannot be tested at one given time.


I'm sure someone else with better knowledge of the ANSI standards can
provide a more comprehensive explanation.

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 |  Next  |  Last
Pages: 1 2 3
Prev: Retive username / machine name
Next: What instances on this box?