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.
| Author |
Topic |
|
shan1430
Yak Posting Veteran
86 Posts |
 Posted - 2008-04-22 : 05:08:00
|
| Hi,I have a table called Past with 4 columns(Number,WW,Goal,Eng) and in Number column has 53 rows, 1,2,3 - 53. Now i need to update the 53rd row with the following code and it works. Since i dont know how to combine this i update them separately. Update Past set WW = ('WW'+convert(varchar(10),datepart(wk,getdate())-1))WHERE Number = '53'Update Pastset Goal = (Select Eng_Goal from AverageEngTime)WHERE Number = '53'Update Pastset Eng = (SELECT ((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 Number = '53'Now, whenever i execute, I want my row 52 to be updated with data from row 53 and row 51 with data from row 52 and so on until row 1 with data from row 2. Meaning i just want to update new data for row 53 and other rows should be updated with the data from next line. How do i do that? Please help..Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-22 : 05:18:00
|
| [code]Update p1 set p1.WW = CASE WHEN p2.Number IS NULL THEN ('WW'+convert(varchar(10),datepart(wk,getdate())-1)) ELSE p2.WW END, p1.Goal = CASE WHEN p2.Number IS NULL THEN (Select Eng_Goal from AverageEngTime) ELSE p2.Goal END, p1.Eng = CASE WHEN p2.Number IS NULL THEN (SELECT ((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') ELSE p2.Eng ENDFROM Past p1LEFT OUTER JOIN Past p2ON p1.Number=p2.Number-1[/code] |
 |
|
|
|
|
|
|
|
update rows