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 Past set Goal = (Select Eng_Goal from AverageEngTime) WHERE Number = '53' Update Past set 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
Add a FROM clause to your UPDATE query, and add a self-join with aliases: UPDATE T1 SET T1.Column1 = T2.Column1 FROM MyTable T1 INNER JOIN MyTable T2 ON T1.Number = T2.Number - 1 After SET, you can have a comma-separated list of assignments, but only for one target table. For the third column, the result should come from a correlated subquery. If you're serious about the Number column being of a character type, then you will at some point run into problems. However, I wonder if you really need to store these results, which are all derived data.
Sorry for the confusion ... Here's a SELECT query to show you how SQL will lookup the next record: SELECT T1.Number AS [Number on Current Row], T2.Number AS [Number on Next Row] FROM MyTable T1 INNER JOIN MyTable T2 ON T1.Number = T2.Number - 1 The UPDATE statement can do batch updates (more than one row at a time). You can also use a FROM clause to bring together data from different tables - or, as in this case, different rows from the same table - exactly the same as in a SELECT query.
