SQL Server Forums
Profile | Register | 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
 New Topic  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  
 New 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.06 seconds. Powered By: Snitz Forums 2000