Updating based up max date

[edwardturner]

Hi,

I am trying to update a certain field within a database table. This SQL statement correctly identifies the records i need to update:

(1)
select max(date) from table
group by IDfield

I cannot do:

update table
set field = 1
where ..

then use a sub select of the statement (1) above because I cannot match the records. Any ideas on how i can get round this?

 

[gmmastros]

Would returning the IDfield from the original select help?

Select Max(date), IDfield
From Table
Group By IDfield



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Here we go modify for your use

create table tableA (IDfield int ,fielda int,TheDate datetime)
insert into tableA
select 0,1, getdate()
waitfor delay '000:00:01'
insert into tableA
select 0,1, getdate()
waitfor delay '000:00:01'
insert into tableA
select 0,2, getdate()
waitfor delay'000:00:01'
insert into tableA
select 0,2, getdate()

select * from tableA

update a set IDfield =1
from tableA a join
(select max(TheDate)as Datemax from tableA
group by fielda) at on a.TheDate =at.Datemax

select * from tableA

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

A little blunder on my part you will have to add the id field in case of different ID's having the same datetime value

update a set IDfield =1
from tableA a join
(select max(TheDate)as Datemax,fielda from tableA
group by fielda) at on a.TheDate =at.Datemax
and a.fielda = at.fielda

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[edwardturner]

That works! Cheers