Update most recent - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Update most recent

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-05 : 22:13:39

create table Mytable (
Mybit bit ,
MyDate Datetime
)

UPDATE MyTable
Set Mybit = 1
WHERE MyDate ???

I want the most recent date.

Thanks,

Sam

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-05 : 22:28:09

Where MyDate = (select max(MyDate) from MyTable)

Does that help ?

Damian

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-06 : 09:52:08

Thanks. I need to use aggregate functions more to better understand their use. I did not realize a select of an aggregate (or scalar) could be used in an equality condition in a where.

Taking the Max () as the WHERE condition, you will see that the WHERE in the full query is executed twice. I wonder if there is a way for the WHERE TO execute only once.

Create table Mytable (
ClientID INT ,
UserID INT ,
CourseID INT ,
MyDate Datetime ,
Mybit bit
)

UPDATE Mytable
SET Mybit = 1
WHERE ClientID=@ClientID and
CourseID = @CourseID and
UserID = @UserID and
MyDate = (SELECT Max(MyDate) From Mytable WHERE ClientID=@ClientID
and UserID=@UserID and CourseID = @CourseID)

If I added an identity field to Mytable, could the Select Max () also return the identity of the Max row - or is this query as optimal as it gets?

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-06 : 10:06:36

The subquery with the aggregate needs to be correlated: its where clause must contain a condition that matches the primary key to the outer table. That being said, I think you query is pretty much optimal. I don't think adding a surrogate key will add any performance.

Jay White
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-06 : 13:12:02

Thanks Jay.

I was so sure there was a better way to do this that I spent more time and came up with what appears to be a better query to human eyes, but has an inferior execution plan in SQL.

UPDATE Mytable
SET Mybit = 1
WHERE MyTableID =
(SELECT Top 1 MyTableID From Mytable
WHERE ClientID=@ClientID
and UserID=@UserID and CourseID = @CourseID
ORDER BY MyDate DESC)

The execution plan for this query appears to have two lookups while the plan for the prior query has one.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources