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
 General SQL Server Forums
 New to SQL Server Programming
 stumped with update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

seeker62
Starting Member

40 Posts

Posted - 04/23/2013 :  10:35:05  Show Profile  Reply with Quote
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

Edited by - seeker62 on 04/23/2013 10:50:19

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/23/2013 :  10:47:42  Show Profile  Reply with Quote
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.
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 04/23/2013 :  10:55:39  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/23/2013 :  11:30:52  Show Profile  Reply with Quote
;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;
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 04/23/2013 :  11:56:34  Show Profile  Reply with Quote
we are getting closer. thanks. rn is 1 only for 1 record. it seems that partition by does not segment out the subgroupings
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/23/2013 :  12:09:35  Show Profile  Reply with Quote
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?
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 04/23/2013 :  12:50:17  Show Profile  Reply with Quote
it is working now thank you so much for your help. Until next time.
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 04/23/2013 :  15:03:33  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/23/2013 :  15:25:26  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/23/2013 :  15:25:29  Show Profile  Reply with Quote
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?
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 04/23/2013 :  17:53:40  Show Profile  Reply with Quote
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.
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.11 seconds. Powered By: Snitz Forums 2000