Query Optimization - SQL Server Forums

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

Query Optimization

Author

Topic

BellaScout
Starting Member

25 Posts

Posted - 2009-02-10 : 17:27:19

Can someone tell me how to optimize this query:

UPDATE table
SET current_rec = 'Y'
WHERE (table_key IN
(SELECT f.table_key
FROM (SELECT COL_3, MAX(COL_DATE) AS maxdate
FROM table AS table_1
GROUP BY COL_3) AS x INNER JOIN
table_1 AS f ON f.COL_3 = x.COL_3 AND f.COL_DATE = x.maxdate))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 17:34:13

Are you using SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

BellaScout
Starting Member

25 Posts

Posted - 2009-02-10 : 17:37:42

Yes I am. Running query in managment studio...ultimately SSIS package.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 17:41:10

[code]UPDATE f
SET Current_Rec = 'Y'
FROM (
SELECT Current_Rec,
ROW_NUMBER() OVER (PARTITION BY Col_3 ORDER BY Col_Date DESC) AS recID
FROM Table1
) AS f
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

BellaScout
Starting Member

25 Posts

Posted - 2009-02-10 : 17:50:16

I receive this error:
The OVER SQL construct or statement is not supported.

BellaScout
Starting Member

25 Posts

Posted - 2009-02-10 : 17:52:10

It works now, not sure why I received that error message the first time I ran it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-10 : 17:52:30

Run his query in a "New Query" window and not in the graphical pane.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BellaScout
Starting Member

25 Posts

Posted - 2009-02-10 : 17:58:52

Yes that works!

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 18:02:54

Do you get the result you expect?
You can also use this to set not current records to 'N'

UPDATE	f
SET	Current_Rec =	CASE recID
				WHEN 1 THEN 'Y'
				ELSE 'N'
			END
FROM	(
		SELECT	Current_Rec,
			ROW_NUMBER() OVER (PARTITION BY Col_3 ORDER BY Col_Date DESC) AS recID
		FROM	Table1
	) AS f

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources