December 21, 2010 at 2:07 am
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
December 21, 2010 at 2:24 am
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 ?
December 21, 2010 at 2:24 am
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
December 21, 2010 at 2:52 am
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.
December 21, 2010 at 2:56 am
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
December 21, 2010 at 2:58 am
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.
December 21, 2010 at 3:03 am
What DATATYPE are your Est and Diff columns , the comma implies that it is not int or numeric.
December 21, 2010 at 3:04 am
Ohh sorry, they are float.
December 21, 2010 at 3:13 am
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
December 21, 2010 at 3:19 am
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
December 21, 2010 at 3:26 am
You're trying to do a running total. Give me a couple of minutes...
-- Gianluca Sartori
December 21, 2010 at 3:51 am
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
*/
December 21, 2010 at 5:52 am
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
December 21, 2010 at 9:04 am
Hi everybody,
i got everything implemented and it works like a charme. Thanks everybody for the support.
Regards, Stephan
December 21, 2010 at 9:13 am
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