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
 Using max in update

Author  Topic 

shezad1
Starting Member

2 Posts

Posted - 2015-02-01 : 01:51:20
Hi

I have one table1 which contain data like this
ID --- year --- months
1 --- 2015 --- null

table2 like this
ID --- Year --- months
1 --- 2015 --- 6
1 --- 2015 --- 7

I want to update the max value of month for id 1,which is 7
using update function, How can i do this.

Thanks.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-01 : 03:56:53
Try this:
update table1
set table1.year=table2.maxYear
,table1.months=table2.maxmonths
from table1
inner join (select ID
,max(Year) as maxYear
,max(months) as maxmonths
from table2
group by ID
) as table2
on table2.ID=table1.ID
and (table2.maxYear<>isnull(table1.year,0)
or table2.maxmonths<>isnull(table1.months,0)
)
In case you have records in table2 which are not present in table1, run this:
insert into table1 (ID,year,months)
select ID
,max(Year)
,max(months)
from table2
where not exists(select *
from table1
where table1.ID=table2.ID
)
group by ID
Go to Top of Page

shezad1
Starting Member

2 Posts

Posted - 2015-02-01 : 04:27:33
Thanks let me try this.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-02-02 : 01:20:17
You do not need a first table. You can just use this

select ID,year,max(month) as month from table2 group by Id, year


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -