Updating a column with a value from above doesnt work

  • Hello everybody,

    at the moment im really confused why something really simple isnt working and i cant think of anything why it shouldn't.

    ID Inv. EstDiff

    116901821508

    21690521638

    316904081282

    41690265,21424,8

    516906001090

    61690181672

    71690181672

    81690921598

    916904,81685,2

    101690501640

    Thats the table I have. What i want to do is update the diff column with id 2. It should be the diff value from id 1 - est from diff 2. So 1508 -52. The SQL I have which should work in my opinion is

    UPDATE tmp_table SET Diff = t.Diff - Est FROM tmp_table AS t WHERE id > t.id <-- doesnt work

    UPDATE tmp_table SET Diff = t.Diff - Est FROM tmp_table AS t WHERE id = t.id-1 <-- doesnt work

    I have no idea how to get my result.

    Regards

  • Can you post the DDL for the table ?

    Also when you say "it doesn't work ", how doesnt it work ? Does it Error, updates zero rows ?



    Clear Sky SQL
    My Blog[/url]

  • Try this:

    UPDATE t1

    SET Diff = t2.Diff - t1.Est

    FROM tmp_table AS t1

    INNER JOIN tmp_table t2

    ON t2.id = t1.id-1

    -- Gianluca Sartori

  • stephan.koeppen (12/21/2010)


    Thats the table I have. What i want to do is update the diff column with id 2.

    Assuming you want to update all rows, not just the one with id = 2, Gianluca's script will work. It is important you understand why you need two references to the table and a join. If it is unclear, ask.

    What bothers me though, is what this data represents, and why you need to do what you are asking.

    There's probably a much better design - if you are interested, tell us some more about what the table is for.

  • SQLkiwi (12/21/2010)


    stephan.koeppen (12/21/2010)


    Thats the table I have. What i want to do is update the diff column with id 2.

    Assuming you want to update all rows, not just the one with id = 2, Gianluca's script will work. It is important you understand why you need two references to the table and a join. If it is unclear, ask.

    What bothers me though, is what this data represents, and why you need to do what you are asking.

    There's probably a much better design - if you are interested, tell us some more about what the table is for.

    Nice catch, Paul.

    I assumed he wanted the whole table updated based on sample data.

    I agree that this can be a case when spoon-feeding some code as I did may not be the best answer...

    -- Gianluca Sartori

  • Hi Guys,

    thanks for your help so far but it didnt work all the way. AFter executing it i get the following result.

    116901821508

    21690521456

    316904081230

    41690265,21016,8

    51690600824,8

    61690181072

    71690181654

    81690921580

    916904,81593,2

    101690501635,2

    i do want to update all the coluns but it seemed to only use it for the second. afterwards the values are messed up and i have no idea where it got it from.

    The table is something which got generated via view for an export where i need to update those fields.

  • What DATATYPE are your Est and Diff columns , the comma implies that it is not int or numeric.



    Clear Sky SQL
    My Blog[/url]

  • Ohh sorry, they are float.

  • Let's work on the sample data you provided:

    IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL DROP TABLE #tmp_table

    ;WITH SampleData (ID, Inv, Est, Diff)

    AS (

    SELECT 1 ,1690 ,182 ,1508

    UNION ALL SELECT 2 ,1690 ,52 ,1638

    UNION ALL SELECT 3 ,1690 ,408 ,1282

    UNION ALL SELECT 4 ,1690 ,265.2 ,1424.8

    UNION ALL SELECT 5 ,1690 ,600 ,1090

    UNION ALL SELECT 6 ,1690 ,18 ,1672

    UNION ALL SELECT 7 ,1690 ,18 ,1672

    UNION ALL SELECT 8 ,1690 ,92 ,1598

    UNION ALL SELECT 9 ,1690 ,4.8 ,1685.2

    UNION ALL SELECT 10 ,1690 ,50 ,1640

    )

    SELECT *

    INTO #tmp_table

    FROM SampleData

    -- BEFORE UPDATE

    SELECT *

    FROM #tmp_table

    UPDATE t1

    SET Diff = t2.Diff - t1.Est

    FROM #tmp_table AS t1

    INNER JOIN #tmp_table t2

    ON t2.id = t1.id-1

    -- AFTER UPDATE

    SELECT *

    FROM #tmp_table

    Does this do the trick? What are we missing?

    Can you post the expected result based on this sample data?

    -- Gianluca Sartori

  • Hi there,

    no, you are not missing anything regards the data but the result is still not correct. 🙂

    thanks so much for your help so far though. anymay, here is the expected result

    11690182.01508.0

    2169052.01456.0

    31690408.01048.0

    41690265.2782,8

    51690600.0182,8

    6169018.0164,8

    7169018.0146,8

    8169092.054,8

    916904.840

    10169050.00

  • You're trying to do a running total. Give me a couple of minutes...

    -- Gianluca Sartori

  • Using Gianluca's sample data :

    UPDATE #tmp_table

    SET Diff = t2.Diff

    FROM #tmp_table t1

    INNER JOIN (

    SELECT a.ID, a.Inv, a.Est, a.Inv-SUM(b.Est) AS Diff

    FROM #tmp_table a

    CROSS JOIN #tmp_table b

    WHERE (b.ID <= a.ID)

    GROUP BY a.ID,a.Est,a.Inv) t2

    ON t1.ID = t2.ID

    SELECT * FROM #tmp_table

    Returns: -

    /*

    ID Inv Est Diff

    ----------- ----------- --------------------------------------- ---------------------------------------

    1 1690 182.0 1508.0

    2 1690 52.0 1456.0

    3 1690 408.0 1048.0

    4 1690 265.2 782.8

    5 1690 600.0 182.8

    6 1690 18.0 164.8

    7 1690 18.0 146.8

    8 1690 92.0 54.8

    9 1690 4.8 50.0

    10 1690 50.0 0.0

    */


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • This is how you could do it with a "quirky update".

    The technique is described in detail here[/url].

    IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL DROP TABLE #tmp_table

    ;WITH SampleData (ID, Inv, Est, Diff)

    AS (

    SELECT 1 ,1690 ,182 ,1508

    UNION ALL SELECT 2 ,1690 ,52 ,1638

    UNION ALL SELECT 3 ,1690 ,408 ,1282

    UNION ALL SELECT 4 ,1690 ,265.2 ,1424.8

    UNION ALL SELECT 5 ,1690 ,600 ,1090

    UNION ALL SELECT 6 ,1690 ,18 ,1672

    UNION ALL SELECT 7 ,1690 ,18 ,1672

    UNION ALL SELECT 8 ,1690 ,92 ,1598

    UNION ALL SELECT 9 ,1690 ,4.8 ,1685.2

    UNION ALL SELECT 10 ,1690 ,50 ,1640

    )

    SELECT Id, Inv, CAST(Est AS float) AS Est, CAST(Diff AS float) AS Diff

    INTO #tmp_table

    FROM SampleData

    SELECT *

    FROM #tmp_table

    CREATE CLUSTERED INDEX IX_RunningTotal ON #tmp_table(ID)

    DECLARE @prev_est float

    SET @prev_est = 0

    DECLARE @Diff float

    SET @Diff = 0

    UPDATE #tmp_table

    SET @Diff = Diff = Diff - @prev_est,

    @prev_est = @prev_est + Est

    FROM #tmp_table WITH (INDEX (IX_RunningTotal), TABLOCKX)

    OPTION (MAXDOP 1)

    SELECT *

    FROM #tmp_table

    -- Gianluca Sartori

  • Hi everybody,

    i got everything implemented and it works like a charme. Thanks everybody for the support.

    Regards, Stephan

  • Glad you sorted out.

    -- Gianluca Sartori

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply