Updating table with data from itself (2008)

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating table with data from itself (2008)

Author

Topic

Skwoerd
Starting Member

5 Posts

Posted - 2011-11-03 : 06:45:45

Hey guys,
I'm rather new to SQL (using 2008) and I'm having a problem:

I have a table called Measurements that contains the columns timestamp and speed. Now i have added a column myself called LastTwenty, that is supposed to display the last time (timestamp) the speed was 20.

I have tried and tried but have yet to find a way to properly fill this column. An example query that i tried:

Update Measurements
SET LastTwenty =
(
SELECT MAX(M2.Timestamp)
FROM Measurements LEFT JOIN Measurements AS M2 ON Measurements.Timestamp=M2.Timestamp
Where M2.Timestamp < Measurements.Timestamp AND M2.speed=20
)

However this just leaves all the LastTwenty values on Null.

I have also tried first setting all values of LastTwenty equal to Timestamp where the speed actually is 20 (this works ofc). And then just updating the value of LastTwenty to the value it has in the previous row. Theoretically this should work as well, but I cant get it to function either.

Any help would be much appreciated!!

Best Regards,
Skwoerd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 07:30:24

[code]Update m
SET m.LastTwenty = m1.Prev20Timestamp
FROM Measurements m
CROSS APPLY (SELECT MAX(Timestamp) AS Prev20Timestamp
FROM Measurements
WHERE Timestamp < m.Timestamp
AND speed=20
)m1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Skwoerd
Starting Member

5 Posts

Posted - 2011-11-04 : 04:04:19

Thanks for the Answer.

I guess it should work, looks good. But atm it's taking very long since i have a sh*tload of data in the table. (Around 4mil rows)

Is there maybe any way to do it more efficiently, or will I just have to start deleting a lot of data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 04:08:39

what all indexes you've on table? also are statistics up to date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Skwoerd
Starting Member

5 Posts

Posted - 2011-11-04 : 04:38:11

I have an index on Timestamp. And i don't have a clue if my statistics are up to date, is there any way to check?

I've allready started deleting data though. I have 10 measurements per second, I'm now deleting 8 out of all those 10 so thats a significant change.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 04:42:09

quote:
Originally posted by Skwoerd

I have an index on Timestamp. And i don't have a clue if my statistics are up to date, is there any way to check?

I've allready started deleting data though. I have 10 measurements per second, I'm now deleting 8 out of all those 10 so thats a significant change.

just do sp_updatestats to update statistics (might take sometime depending on data/indexes on tables)
Check if its using index from execution plan

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Skwoerd
Starting Member

5 Posts

Posted - 2011-11-04 : 05:43:40

I just reduced the data from 4mil rows to 800k rows, that should help.

I updated statistics. I checked my estimated execution plan, it showed that 59% cost is from updating a clustered index on Timestamp and trainID (which is a column im not using atm).

Skwoerd
Starting Member

5 Posts

Posted - 2011-11-04 : 06:25:34

IT WORKS!!

Thanks man!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 07:53:49

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-04 : 15:06:58

the only predicate for the CROSS APPLY is on Timestamp column and speed. Speed is probably the more selective since you are stating speed = 20

Assuming you only want to do this once for the x million rows

1) create nonclustered index on the column (in fact I'd do CREATE NONCLUSTERED INDEX IX_Measurements_Speed_timestap ON Measurements (Speed, Timestamp))
2) run update
3) drop index made in (1) (unless it's useful itself for other queries)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources