SQL Server 2005, SQL 2008 Update Statement

countrymeister
countrymeister used Ask the Experts™
on
I want to update a table T1 based on a select on table T2 and T3

I want to make sure the update does not fail based on multiple rows that could be possible from the select clause

 update TI
set T1.MarketPrice= t3.Price
from       table2 t2 , table3 t3
where       t2.MarketPrice is null
and       t3.BusDate            =      @vPrevBusDate
and       t3.GroupID      =      t2.GroupID

 T3 has the foll columns
GroupID, BusDate, RateTier, region, price
( key of Group, date and ratetier, region)
Sample data
Group1, 10/29/2010, Rate1, East, 1
Group1, 10/29/2010, Rate1, west, 2
Group1, 10/28/2010, Rate1, East, 3
Group1, 10/28/2010, Rate1, west, 4

T2 has the following columns
GroupID, MarketPrice
(Key groupID)

The select clause for Group1 for 10/28/2009, could return two rows with prices 3 or 4.
I want to make sure that the update does not fail due to the select clause returning multiple rows
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr. System Analyst
Commented:
try this:
update TI 
   set T1.MarketPrice= t3.Price
  from table2 t2, (select distinct BusDate, GroupID from table3) t3
 where t2.MarketPrice is null
   and t3.BusDate = @vPrevBusDate
   and t3.GroupID = t2.GroupID

Open in new window

SharathData Engineer
Commented:
It is clear that you have 2 records for the same GroupID and BusDate in table3. Which Price you want to consider? If it is the max value, you can try like this. In case if you want to update with Min value, replace Max with Min.
update TI
   set T1.MarketPrice= t3.Price
  from table2 t2
  join (select GroupID,BusDate,max(price) as price from table3 group by GroupID,BusDate) t3
    on t3.GroupID = t2.GroupID
 where t2.MarketPrice is null
   and t3.BusDate = @vPrevBusDate

Open in new window

Author

Commented:
Thanks for both your responses
What I noticed is that the update does not fail, even if there are multiple rows in the select qulification, it just takes the minimum price.
I was wondering why does the update does not fail.

update TI
   set T1.MarketPrice= t3.Price
  from table2 t2 , table t3
    on t3.GroupID = t2.GroupID
 where t2.MarketPrice is null
   and t3.BusDate = @vPrevBusDate

Author

Commented:
thank you both for your input

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial