Update Using Max value - 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

Update Using Max value

Author

Topic

skarumuru
Starting Member

2 Posts

Posted - 2015-05-06 : 17:12:52

doc_id --- version --- activate_doc
d1 --- 1 --- N
d1 --- 2 --- N
d1 --- 3 --- N
d2 --- 1 ---N
d2 --- 3 ---N
d2 --- 4 ---N
d3 --- 1 ---N
d3 --- 2 ---N

I want to update "activate_doc=Y" where the version having Max value based on the doc_id.

expected results:
d1 --- 3 --- Y
d2 --- 4 ---Y
d3 --- 2 ---Y

Please post the query. Appreciate your help in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-06 : 17:20:48

with version (doc_id, max_version)
as (select doc_id, max(version) as max_version from yourtable group by doc_id)
update yourtable
set activate_doc = 'Y'
from yourtable
join version on yourtable.doc_id = version.doc_id and yourtable.version = version.max_version
where activate_doc <> 'N'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

skarumuru
Starting Member

2 Posts

Posted - 2015-05-06 : 17:32:17

I am sorry.

Is it a single query to execute on SQL Commander or its a Stored procedure?

quote:
Originally posted by tkizer

with version (doc_id, max_version)
as (select doc_id, max(version) as max_version from yourtable group by doc_id)
update yourtable
set activate_doc = 'Y'
from yourtable
join version on yourtable.doc_id = version.doc_id and yourtable.version = version.max_version
where activate_doc <> 'N'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-06 : 17:41:28

Yes it's a single query. But SQL Commander? SQLTeam.com is for Microsoft SQL Srever. Which dbms are you using?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources