Updating rows of a column

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

1. shan1430New 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' )

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' )