While Loop

Question

Post reply

While Loop

datelligence

Say Hey Kid

Points: 672
More actions
September 23, 2009 at 8:18 am

#214692

HI, Actually I need to update a Fact Table, I´m trying this statement
while (select id_promotionSub FROM dbo.sipFTdataStage)=5
begin
update dbo.sipFTdataStage
set net_price = 55
end
but I get an error message as follows:
"Subquery returned mor than 1 value. This is not permitted when the subquery follows =. !=, ....or when de subquery is used as an expression"
Please give me a hand, I really don´t understand what is hapenning,
Thanks,
Jorge

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

The problem is that your sub-query is returning more than one value so it cannot resolve to equal 5

eg.

while (select id_promotionSub FROM dbo.sipFTdataStage)=5

can return a list of values and this cannot work in a sub-query with '=' as SQL does not know what value to evaluate.

Using a while-loop is not recomended in T-SQL and the query you are using looks like it could easily be written in a set-based manner without a while loop.

update dbo.sipFTdataStage

set net_price = 55

WHERE sipFTdataStage.id_promotionSub = 5

Apurva SSCrazy Points: 2412 More actions · Answer 2

From purely programing perspective: I dont think this while loop will terminate

while (select id_promotionSub FROM dbo.sipFTdataStage)=5

begin

update dbo.sipFTdataStage

set net_price = 55

end

Steve is right in the previous post.

datelligence Say Hey Kid Points: 672 More actions · Answer 3

Hi steveb, understand your explanation. Thanks form that. The table I want to update is a fact table in the data stage area. For each value that has a foreign key with different values, I need to apply different values, so for example, (i) If id_promotionSub values are in (1,2) then need to update the field net_price with a simple computation like =initial_price-monetary_value, (ii) if d_promotionSub values are in (3,4) then need to update net_price=(initial_price-(initial_price*10/100)).

That´s the reason because of I tried to use the while loop.

So what can you guys suggest me to do? Don´t want to deal with script language to update the table, i´ve done somethng like that and generates a very large cartesian product which means a long query and a very large load to the server.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Sounds like a case statement being used in the update. How many differnet values of id_promotionSub are there? How many rows of data in the fact table?

datelligence Say Hey Kid Points: 672 More actions · Answer 5

Sounds like a case statement being used in the update. How many differnet values of id_promotionSub are there? How many rows of data in the fact table?

Hi, there are no more than 200 possible id_promotionSub values. Tha fact table has about 20000 rows.

steveb. SSC-Forever Points: 46733 More actions · Answer 6

As mentioned I would use an update with case statement;

UPDATE dbo.sipFTdataStage

SET net_price =

(CASE

WHEN id_promotionSub IN (1,2) THEN initial_price - monetary_value

WHEN id_promotionSub IN (3,4) THEN initial_price - (initial_price*10/100)

END

)

If these changes happen regularly then I would look at putting the update rules into a table and then using this for the updates.

datelligence Say Hey Kid Points: 672 More actions · Answer 7

Thanks steveb, i´ve just used your advice and it´s working fine.