stumped with 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

stumped with update

Author

Topic

seeker62
Starting Member

40 Posts

Posted - 2013-04-23 : 10:35:05

two tables

table1
contractnum contractmonth contractyear contractamt so
pp1 11 2008 null 1
pp1 11 2008 null 2
pp1 11 2008 null 3
pp1 12 2008 null 4
pp1 12 2008 null 5

table2
contractnum month year amt
pp1 11 2008 10
pp1 12 2008 20
pp1 1 2009 10
pp2 11 2008 25

i 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 tables

table1
contractnum contractmonth contractyear contractamt so
pp1 11 2008 null 1
pp1 11 2008 null 2
pp1 11 2008 null 3
pp1 12 2008 null 4
pp1 12 2008 null 5

table2
contractnum month year amt
pp1 11 2008 10
pp1 12 2008 20
pp1 1 2009 10
pp2 11 2008 25

i 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  so
pp1            11              2008           10         1
pp1            11              2008           null       2
pp1            11              2008           null       3
pp1            12              2008           20         4
pp1            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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources