Updating rows of a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating rows of a column

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-10 : 04:58:43

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 05:02:39

Subquery should return maximum one column and one row when you are using it for update purpose. Post some sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-10 : 05:10:34

sample output in Pastweeks, 7 is the Eng_Goal that should be updated in "Goal" and the result of the calculation should be updated in "Used" row.

Weeks|Week1|Week2.....|Week14
Goal...|.........|..............|7
Used..|.........|..............|3.5

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 05:24:39

Is there any relation or common column between these two tables - PastWeeks and AverageEngTime ?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-10 : 05:25:50

shan1430

As an aside to your question, I recommend you read up a bit on database normalization. This will get you started, and there's plenty of further reading at the bottom...

http://en.wikipedia.org/wiki/Database_normalization

The structures you seem to be working with are going to cause you more headaches than you need.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 05:28:17

[code]UPDATE pw
SET pw.Week14 =
FROM PastWeeks AS pw
INNER JOIN (
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.0 / 168.0
FROM AverageEngTime
WHERE Shifts = 'Average'
) AS x ON x.Eng_Goal = pw.Weeks
WHERE pw.Weeks IN ('Goal', 'Used')[/code]

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-10 : 21:31:46

I received the following error for youe query..

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 05:40:18

quote:

Originally posted by Peso

UPDATE		pw
SET		pw.Week14 = new_value
FROM		PastWeeks AS pw
INNER JOIN	(
			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.0 / 168.0 as new_value
			FROM		AverageEngTime
			WHERE		Shifts = 'Average'
		) AS x ON x.Eng_Goal = pw.Weeks
WHERE		pw.Weeks IN ('Goal', 'Used')

E 12°55'05.25"
N 56°04'39.16"

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 07:05:53

Thanks Madhi!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources