September 17, 2010 at 2:43 pm
i have table having field date along with address i need to update the date dynamically for each duplicate address
for example
Addr date
address1 2010-09-15
address1 2010-09-15
address1 2010-09-15
this should be
Addr date
address1 2010-09-15
address1 2010-09-16
address1 2010-09-16
September 17, 2010 at 2:49 pm
Do you have any way of determining what is the "correct" row to keep? Is there a PK, and can you provide some DDL for the table?
September 17, 2010 at 2:54 pm
yes i have addrid
Addrid Addr date
1 address1 2010-09-15
2 address1 2010-09-15
3 address1 2010-09-15
September 17, 2010 at 7:23 pm
I had to test this code in 2005, but it should perform identically in 2008
Your data with a few entries added
CREATE TABLE #T(Addrid INT, Addr VARCHAR(15), datex DATETIME)
INSERT INTO #T
SELECT 1,'address1','2010-09-15' UNION ALL
SELECT 2,'address1','2010-09-15' UNION ALL
SELECT 3,'address1','2010-09-15' UNION ALL
SELECT 4,'addres2','2010-09-15' UNION ALL
SELECT 5,'addres2','2010-09-15'
This is code for you to use in testing to verify that this is giving you the entries that are subject to being updated
;with numbered as(SELECT rowno=row_number() over
(partition by Addr order by Addrid),Addrid,Addr,datex from #T)
select * from numbered
The Result:
rownoAddridAddr datex
14addres2 2010-09-15 00:00:00.000
25addres2 2010-09-15 00:00:00.000
11address12010-09-15 00:00:00.000
22address12010-09-15 00:00:00.000
33address12010-09-15 00:00:00.000
Once you have checked the values returned then you can test the update code:
;with numbered as(SELECT rowno=row_number() over
(partition by Addr order by Addrid),Addrid,Addr,datex from #T)
UPDATE numbered SET datex = DATEADD(dd ,1, datex) WHERE rowno > 1
SELECT * FROM #T
The SELECT * FROM #T returns:
AddridAddr datex
1address12010-09-15 00:00:00.000
2address12010-09-16 00:00:00.000
3address12010-09-16 00:00:00.000
4addres2 2010-09-15 00:00:00.000
5addres2 2010-09-16 00:00:00.000
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply