While Loop

• datelligence

  Say Hey Kid

  Points: 672

  More actions

  September 23, 2009 at 8:16 am

  #210036

  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

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  September 23, 2009 at 8:40 am

  #1057559

  jeardilam (9/23/2009)

  ---

  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

  The select statement you have is returning multiple values. If you're trying to compare the id_promotionSub value to 5, then you should just write the update like this:

  UPDATE dbo.sipFTdataStage

  SET net_price = 55

  WHERE id_promotionSub = 5

  That's going to work a lot faster since it's not a RBAR (row by agonizing row) process.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning