SQL Server Performance

Updating rows of a column

Discussion in 'Getting Started' started by shan1430, Apr 10, 2008.

  1. shan1430 New Member

    Hi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.
    Update Pastweeks
    set
    week14 =
    (SELECT
    Eng_Goal,
    ((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day
    + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168)
    FROM AverageEngTime where Shifts = 'Average')
    where Weeks = ('Goal','Used' )
  2. madhuottapalam New Member

    Update Pastweeks set week14 = (SELECT Eng_Goal, ((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average') where Weeks = ('Goal','Used' )
    When you update like this the select should return only one expression or column. Here you have ENG_GOAL and ((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168)
    Concatinate it in the select. Ie something like this
    Update Pastweeks set week14 = (SELECT Eng_Goal +' '+Cast( ((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168 as Varchar(10)) FROM AverageEngTime where Shifts = 'Average') where Weeks = ('Goal','Used' )
    Madhu
  3. shan1430 New Member

    Hi, when i use your query it says error near keyword "AS". How to fix this? thanks
  4. madhuottapalam New Member

    Post back the queryMadhu

Share This Page