select and update table

• manojkanoi

  SSCrazy

  Points: 2292

  More actions

  December 2, 2004 at 12:53 am

  #161955

  Hi,

  I am trying to use the results from a select statement to update a table. I want to be able to do it faster just using sql server and not ASP.Right now I am doing it using ASP in the following way:

  rsSalessql = "SELECT SeqNumberTemp.ProductNameFull, (CASE WHEN OrderTotal > 0 THEN (SalesTotalTemp.OrderTotal * SeqNumberTemp.margin) ELSE SeqNumberTemp.margin END) AS Total FROM SalesTotalTemp RIGHT OUTER JOIN SeqNumberTemp ON SalesTotalTemp.ProductNameFull2 = SeqNumberTemp.ProductNameFull ORDER BY Total desc"

   

   Set rsSales = SessionData.Execute(rsSalessql)

   seq = 0

   While not rsSales.EOF

    seq = seq + 6

    sql = "update Products SET salesseq = '" & seq &"' WHERE Manufacturer + ' ' + ProductName = '" & rsSales("ProductNameFull") & "'"

    SessionData.Execute(sql)

   rsSales.MoveNext

   Wend

  Can you please so that I can make this work faster just using sql server.

• December 6, 2004 at 8:00 am

  This was removed by the editor as SPAM

• kay

  SSCommitted

  Points: 1518

  More actions

  December 13, 2004 at 2:10 am

  #533476

  manojkanoi,

  I can't do the complete job for you, but here's a suggestion how to construct an UPDATE...FROM query in general:

  update ProductAlias

  set .....

  FROM Products ProductAlias

  inner join SalesTotalTemp

  RIGHT OUTER JOIN SeqNumberTemp ON ...

  WHERE ...

  Kay