SQL Server Performance

Update Rows

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

  1. shan1430 New Member

    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
  2. Adriaan New Member

    Add a FROM clause to your UPDATE query, and add a self-join with aliases:
    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.
  3. shan1430 New Member

    Hi, i dont really understand how to do this. Number column will only contain numbers from 1 to 53.
  4. Adriaan New Member

    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.

Share This Page