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

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

shezad1
Starting Member

2 Posts

Posted - 2015-02-01 : 04:27:33

Thanks let me try this.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources