Author |
Topic |
seeker62
Starting Member
40 Posts |
Posted - 2013-04-23 : 10:35:05
|
two tablestable1contractnum contractmonth contractyear contractamt sopp1 11 2008 null 1pp1 11 2008 null 2pp1 11 2008 null 3pp1 12 2008 null 4pp1 12 2008 null 5table2contractnum month year amtpp1 11 2008 10pp1 12 2008 20pp1 1 2009 10pp2 11 2008 25i want to update table1.contractamt with table2.amt where month and year are same but just for one record of the month year set not all of them. I have tried rownumber (partition by) i have tried correlation and nothing is working. I have also tried while @@fetch_staus = 0 to evaluate each reacord and then make a decision and this is not an option due to performance degradation. I know i have posted here with this similar problem before but workable solutions are not forthcoming. thanks |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-23 : 10:47:42
|
quote: Originally posted by seeker62 two tablestable1contractnum contractmonth contractyear contractamt sopp1 11 2008 null 1pp1 11 2008 null 2pp1 11 2008 null 3pp1 12 2008 null 4pp1 12 2008 null 5table2contractnum month year amtpp1 11 2008 10pp1 12 2008 20pp1 1 2009 10pp2 11 2008 25i want to update table1.contractamt with table2.amt where month and year are same but just for one record of the month year set not all of them. I have tried rownumber (partition by) i have tried correlation and nothing is working. I know i have posted here with this similar problem before but workable solutions are not forthcoming. thanks
Can you show what table1 would look like after a successful update? BTW, if you use place tabular data within [code] and [/code] that would preserve white spaces, making it easier to read. |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2013-04-23 : 10:55:39
|
table1 needs to look like:contractnum contractmonth contractyear contractamt sopp1 11 2008 10 1pp1 11 2008 null 2pp1 11 2008 null 3pp1 12 2008 20 4pp1 12 2008 null 5 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-23 : 11:30:52
|
[code];with cte as( select t1.contractamt, t2.amt, row_number() over (partition by t1.contractmonth,t1.contractyear order by so) as RN from table1 t1 inner join table2 t2 on t1.contractmonth = t2.month and t1.contractyear = t2.year and t1.contractnum = t2.contractnum )update cte set contractamt = amt where RN=1;[/code] |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2013-04-23 : 11:56:34
|
we are getting closer. thanks. rn is 1 only for 1 record. it seems that partition by does not segment out the subgroupings |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-23 : 12:09:35
|
Presence of contractmonth in the partition clause should ensure that contract months 11 and 12 are treated separately, resulting in RN=1 in each case. Other than contractnum, contractmonth and contractyear (which are all in the partition by clause), are there any other columns that would participate in separating out subgroups? |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2013-04-23 : 12:50:17
|
it is working now thank you so much for your help. Until next time. |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2013-04-23 : 15:03:33
|
i put the above code into a stored procedure and it did not update the records. I run it outside of the stored procedure and it does update the records. What needs to be done so that it will run in the stored procedure? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-23 : 15:25:26
|
I'm assuming you executed the stored proc. Can you post the code for the sproc? It's hard to tell wihout any insight into your code. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-23 : 15:25:29
|
There is nothing about the statement itself that should prevent it from working exactly the same way when put into a stored procedure. Are you using any parameters that you passed in to filter the query, or modify the query based on such parameters, that could have an impact. Can you post the code for the stored proc? |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2013-04-23 : 17:53:40
|
placement, placement, placement. I put the code at close to the end of the sp and it works like a dream why i do not know the important thing it works. Thanks again. |
|
|
|