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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 update with other rows function

Author  Topic 

nirnir
Starting Member

10 Posts

Posted - 2013-01-10 : 09:15:01
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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-10 : 09:28:29
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 - 2013-01-10 : 10:15:00
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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-10 : 11:04:24
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 - 2013-01-10 : 11:49:13
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
   

- Advertisement -