Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 update with other rows function
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nirnir
Starting Member

10 Posts

Posted - 01/10/2013 :  09:15:01  Show Profile  Reply with Quote
I have a tableA with fields : Group, val, max_val,low_val
I need fields max_val,low_val to be computed : max_val= max(val) , low_val=low(val) for the same group .
Attached a table with sample values .

1. please help me with the most efficient update command .
2. considering the table is updated once a month , would it be
better to create a view and have those fields (low_val,high_val)
only in the view or adding them to the real table and recompute
them after each update ?


group __ val__ Low_val__ Max_val
abc ____2________ 2 ____ 5
abc ____4________ 2 ____ 5
abc ____5________ 2 ____ 5
def ____1________ 1 ____ 2
def ____2________ 1 ____ 2
ggg ____5________ 5 ____ 5


Edited by - nirnir on 01/10/2013 09:23:03

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/10/2013 :  09:28:29  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
select t1.*, t2.low_val, t2.max_val
from tbl t1
join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2
on t1.[group] = t2.[group]

for an update
update tbl
set low_val = t2.low_val, max_val = t2.max_val
from tbl t1
join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2
on t1.[group] = t2.[group]

No need for a view.

I would have thought a monthly updae would be simplest but depends on the table size and frequency of updates

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nirnir
Starting Member

10 Posts

Posted - 01/10/2013 :  10:15:00  Show Profile  Reply with Quote
Thanks,
If I want another boolean field IS_MAX_VAL which should be
true in rows where val=max_val
can I update it in the same command ?
(Actually my final aim is to have this field(IS_MAX_VAL ) in the index since most of the times I need to query by it)
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/10/2013 :  11:04:24  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
update tbl
set low_val = t2.low_val, max_val = t2.max_val, is_max_val = case when max_val = t2.max_val then 'Y' else 'N' end
from tbl t1
join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2
on t1.[group] = t2.[group]

A candidate for a filtered index?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nirnir
Starting Member

10 Posts

Posted - 01/10/2013 :  11:49:13  Show Profile  Reply with Quote
Thanks Again,
Regarding filtered index,

In most cases I want to see only records which is_max_val = true (plus other conditions , for example clientNum=x).
I have a primary index on clientNum and few other indexes for
sorting (clientName,dates...)

- should I add another regular index for is_max_val ?
- if I add a filtered index for is_max_val
should the filter expression be is_max_val ?
what should be in the included columns,index columns ?

sorry for my newbie questions
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000