# Need help with another query [Answered]RSS

## 3 replies

Last post Mar 19, 2015 11:32 AM by urir10

• urir10

Member

55 Points

260 Posts

### Need help with another query

Mar 17, 2015 03:02 PM|urir10|LINK

Hoping someone can help me here again.

I have the following table

Policy
Plan Code
Rank
Status
Rate

A policy has multiple plans in different status with different rates and codes. The rank is just an incermental value based on a different logic that goes from 0 to the number of plans on the policy.

What i need to do is update the Rate field for a certian Plan code only when the Plan Code with Rank 0 is equal to a specific value.
For example:

Policy - Plan Code - Rank - Status - Rate
11111 - A - 0 - A - 50
11111 - F - 1 - A - 30
11111 - F - 2 - A - 30
11111 - F - 3 - A - 30

So if Plan code with Rank 0 is A then update all Rates for Plan code F

• gimimex

Contributor

5460 Points

1123 Posts

### Re: Need help with another query

Mar 17, 2015 03:50 PM|gimimex|LINK

Try something like this:

```update t
set t.Rate = a.Rate
from MyTable as t
inner join MyTable as a
on a.Policy = t.Policy and
a.Rank = 0 and
a.PlanCode = 'A'
where
t.Rank > 0 and
t.PlanCode = 'F'```

Hope this helps.

• Edwin Guru S...

Star

8544 Points

1376 Posts

### Re: Need help with another query

Mar 18, 2015 05:04 AM|Edwin Guru Singh|LINK

#### urir10

So if Plan code with Rank 0 is A then update all Rates for Plan code F

As per my understanding of your case, you can try the following query :

```create table tblName(
Policy int,
PlanCode varchar(5),
[Rank] int,
[Status] varchar(5),
Rate int)
-------------------------
insert into tblName values
(1111,'A',0,'A',50),
(1111,'F',1,'A',30),
(1111,'F',2,'A',30),
(1111,'F',3,'A',30);
-------------------------
select * from tblName
-------------------------

update a set a.Rate=
ISNULL( (select distinct rate from tblName where PlanCode='A' and [Rank]=0) ,a.Rate)
from tblName[a] where a.PlanCode='F' and a.[Rank]<>0```

--
with regards,
Edwin

with regards,
Edwin
• urir10

Member

55 Points

260 Posts

### Re: Need help with another query

Mar 19, 2015 11:32 AM|urir10|LINK

gimimex's solution worked great for me. Thanks!

Edwin, in your solution it did not group the policy's together so it updated for all policies where it found 'F'

So if you have this:

```(1111,'A',0,'A',50),
(1111,'F',1,'A',30),
(1111,'F',2,'A',30),
(1111,'F',3,'A',30);```
```(2222,'B',0,'A',50),
(2222,'F',1,'A',30),
(2222,'F',2,'A',30),
(2222,'F',3,'A',30);```

It will update 1111 and 2222