June 9, 2011 at 7:48 am
Can somebody help me with a stored proc that will go through a table and perform updates based on matching records. For example if my table has these fields:
USER_ID | ACCOUNT_ID | TEL | JOIN_DATE |
0001 | c0001 | x | 01/06/2011
0001 | c0001 | x | 23/12/2011 |
So if I have 2 records that have the same user_id and same account_id I want the join_date to be updated so that it is the earliest date, so in the example above the the 2nd row would be updated to 01/06/2011.
Thanks
June 9, 2011 at 8:16 am
declare @t table (USERID char(4), ACCOUNT_ID varchar(5), TEL char(1), JOIN_DATE smalldatetime)
insert into @t
select '0001','c0001','x','01/06/2011' union all
select '0001','c0001','x','12/23/2011'
select * from @t
update t
set t.JOIN_DATE = j.Join_Date
from @t t
inner join (select UserID, Account_ID, MIN(Join_Date) Join_Date
from @t
group by UserID, Account_ID) j
on t.USERID = j.USERID
and t.ACCOUNT_ID = j.ACCOUNT_ID
select * from @t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 9, 2011 at 8:58 am
Thanks very much for this. One more quick question, when performing a task such as this, would it be always best to use this type of stored procedure or could a cursor be used?
Thanks
June 9, 2011 at 10:17 am
Just 1 more question (and thanks so much for your help)
If my table had another field (JOIN_TYPE):
USER_ID | ACCOUNT_ID | TEL | JOIN_DATE | JOIN_TYPE |
0001 | c0001 | x | 01/06/2011 | 2.00 |
0001 | c0001 | x | 23/12/2011 | 2.00 |
0001 | c0001 | x | 23/12/2011 | 3.00 |
How would I adjust the logic so that the update only took place with the same conditions but only if the JOIN_TYPE = 2.00
would I just need to add:
declare @t table (USERID char(4), ACCOUNT_ID varchar(5), TEL char(1), JOIN_DATE smalldatetime)
insert into @t
select '0001','c0001','x','01/06/2011' union all
select '0001','c0001','x','12/23/2011'
select * from @t
update t
set t.JOIN_DATE = j.Join_Date
from @t t
inner join (select UserID, Account_ID, MIN(Join_Date) Join_Date
from @t
group by UserID, Account_ID) j
on t.USERID = j.USERID
and t.ACCOUNT_ID = j.ACCOUNT_ID
and t.JOIN_TYPE = j.JOIN_TYPE
WHERE t.JOIN_TYPE '2.00' and j.JOIN_TYPE = '2.00'
select * from @t
June 9, 2011 at 12:34 pm
Close, but you first have to put the join_type column in the table and populate it.
declare @t table (
USERID char(4),
ACCOUNT_ID varchar(5),
TEL char(1),
JOIN_DATE smalldatetime,
join_type decimal (5,2))
insert into @t
select '0001','c0001','x','01/06/2011', 2.00 union all
select '0001','c0001','x','12/23/2011', 2.00 union all
select '0001','c0001','x','12/23/2011', 3.00
select * from @t
update t
set t.JOIN_DATE = j.Join_Date
from @t t
inner join (select UserID, Account_ID, Join_Type, MIN(Join_Date) Join_Date
from @t
group by UserID, Account_ID, Join_Type) j
on t.USERID = j.USERID
and t.ACCOUNT_ID = j.ACCOUNT_ID
and t.JOIN_TYPE = j.JOIN_TYPE
WHERE t.JOIN_TYPE = '2.00'
select * from @t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 10, 2011 at 2:29 am
Ahh yes, I see.
Many many many thanks!!! BTW, do you know of a website or book that covers this kind of tsql
cheers!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply