February 28, 2011 at 8:08 am
I was wondering if someone could help me. I need to update the contents of a field with a value from another:
I have the following:
Docid,AttachPID,SortDate,Date
0001,77,00/00/0000, 07/02/2011
0002,77,00/00/0000, 02/01/2011
0003,77,00/00/0000, 02/01/2011
0004,77,00/00/0000, 02/01/2011
0005,78,00/00/0000, 09/02/2011
0005,78,00/00/0000, 09/01/2011
0005,78,00/00/0000, 09/01/2011
0005,78,00/00/0000, 09/01/2011
Basically, what I need to do is update the sortdate field with the first value of the date field until the AttachPID changes. So that the table looks like this:
Docid,AttachPID,SortDate,Date
0001,77,07/02/2011, 07/02/2011
0002,77,07/02/2011, 02/01/2011
0003,77,07/02/2011, 02/01/2011
0004,77,07/02/2011, 02/01/2011
0005,78,09/02/2011, 09/02/2011
0005,78,09/02/2011, 09/01/2011
0005,78,09/02/2011, 09/01/2011
0005,78,09/02/2011, 09/01/2011
Thank you in advance.
February 28, 2011 at 8:31 am
You should change this so that the SortDate is stored in the table that it actually applies to, which appears to be the one that AttachPID connects to. The whole point of Normalization is that you should only have update the data in one place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 28, 2011 at 8:38 am
It does Sir, The last table is the result I am looking for. The values for the sortdate field are all 00/00/0000. I want to update these values with the first value in the Date field.
..:-)
February 28, 2011 at 8:41 am
Can you provide table definitions for the two tables?
If so, I can help write an update statement.
If not, it'll be easiest to get it done by querying the outer table in a CTE, using Min and the appropriate FK column, then use that in an Update From query to update the parent table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply