Query Problem when Updating temporary table

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  December 7, 2011 at 3:42 am

  #391058

  Hi,

  I am trying to update a temporary table and am returning the wrong result.

  My 2 tables are

  @tmpOutput

  ItemId,MarketPriceSource,StartDate,EndDate,CollectGoods,W_house

  P12000,Missing,2012-05-01,2012-05-31,0,CUP

  P12000,Missing,2012-06-01,2012-06-30,0,CUP

  P12000,Missing,2012-07-01,2012-07-31,0,CUP

  AND

  Market_Prices_Haulage_Rate

  Item,Destination,Start_Date,End_Date, Cost,Source

  P12000,BUR,2010-01-01,2010-12-31,4.75,South East

  P12000,BUR,2011-01-01,2012-12-31,4.75,South East

  P12000,CUL,2010-01-01,2010-12-31,5.7,Portbury

  P12000,CUL,2011-01-01,2012-12-31,5.7,Portbury

  P12000,CUP,2010-01-01,2010-12-31,7.5,Glasgow

  P12000,CUP,2011-01-01,2012-12-31,8.1,Glasgow

  My code is:

  UPDATE @tmpOutput

  SET MarketPriceSource = (SELECT TOP 1 hr.Source

  FROM Market_Prices..Market_Prices_Haulage_Rate hr WITH( NOLOCK) LEFT JOIN @tmpOutput t1 ON

  t1.ItemId = hr.Item

  AND t1.W_house = hr.Destination

  WHERE t1.StartDate >= hr.Start_Date

  AND t1.EndDate <= hr.End_Date

  AND t1.MarketPriceSource = 'Missing'

  AND t1.CollectGoods = 0)

  But in the above example I need to return 'Glasgow' but am getting a different result.

  Any ideas please?

  Thanks in advance,

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  December 7, 2011 at 3:45 am

  #1417922

  You will need an ORDER BY in your sub-query for the TOP 1 to work poperly.

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  December 7, 2011 at 7:14 am

  #1418070

  You're going about it wrong.

  Try this

  BEGIN TRAN

  --Sample data

  SELECT ItemId, MarketPriceSource, StartDate, EndDate, CollectGoods, W_house

  INTO #tmpOutput

  FROM (SELECT 'P12000', 'Missing', '2012-05-01', '2012-05-31', 0, 'CUP'

  UNION ALL SELECT 'P12000', 'Missing', '2012-06-01', '2012-06-30', 0, 'CUP'

  UNION ALL SELECT 'P12000', 'Missing', '2012-07-01', '2012-07-31', 0, 'CUP') a(ItemId, MarketPriceSource, StartDate, EndDate, CollectGoods, W_house)

  --Sample data

  SELECT Item, Destination, Start_Date, End_Date, Cost, Source

  INTO #Market_Prices_Haulage_Rate

  FROM (SELECT 'P12000', 'BUR', '2010-01-01', '2010-12-31', 4.75, 'South East'

  UNION ALL SELECT 'P12000', 'BUR', '2011-01-01', '2012-12-31', 4.75, 'South East'

  UNION ALL SELECT 'P12000', 'CUL', '2010-01-01', '2010-12-31', 5.7, 'Portbury'

  UNION ALL SELECT 'P12000', 'CUL', '2011-01-01', '2012-12-31', 5.7, 'Portbury'

  UNION ALL SELECT 'P12000', 'CUP', '2010-01-01', '2010-12-31', 7.5, 'Glasgow'

  UNION ALL SELECT 'P12000', 'CUP', '2011-01-01', '2012-12-31', 8.1, 'Glasgow') a(Item, Destination, Start_Date, End_Date, Cost, Source)

  --Update table

  UPDATE t1

  SET t1.MarketPriceSource = hr.Source

  FROM #Market_Prices_Haulage_Rate hr

  INNER JOIN #tmpOutput t1 ON t1.ItemId = hr.Item AND t1.W_house = hr.Destination

  WHERE t1.StartDate >= hr.Start_Date

  AND t1.EndDate <= hr.End_Date

  AND t1.MarketPriceSource = 'Missing'

  AND t1.CollectGoods = 0

  --Check result

  SELECT * FROM #tmpOutput

  /*--------------------------Returns---------------------------------*

  ItemId MarketPriceSource StartDate EndDate CollectGoods W_house

  ------ ----------------- ---------- ---------- ------------ -------

  P12000 Glasgow 2012-05-01 2012-05-31 0 CUP

  P12000 Glasgow 2012-06-01 2012-06-30 0 CUP

  P12000 Glasgow 2012-07-01 2012-07-31 0 CUP

  \-------------------------------------------------------------------*/

  ROLLBACK

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---