self updated in field in same table
-
I have a horrible manual process to that I want to automate as much as possible. The query has been driving me crazier than I normally am.
here is a sample of the data in a table called projects.
ContDir ORG
xxxx aaaa
xxxx null
yyy bbbb
yyy bbbb
yyy null
zzzz null
I want to populate the org field based the ContDir. so the result will look like this. Then all I have to look up the ContDir for ZZZZ.
ContDir ORG
xxxx aaaa
xxxx aaaa
yyy bbbb
yyy bbbb
yyy bbbb
zzzz null
How do I do this?
-
OK, I made this hard than I should have.
My solution was to create temp table with distinct CONTDIR, and ORG where ORG is not null and them run a update against the temp table. Worked like a champ.
-
Umm... How about
Update t
SET ORG = t2.ORG
FROM tbl t
JOIN (SELECT DISTINCT ContDir, ORG FROM tbl WHERE ORG IS NOT NULL) t2
ON t.ContDir = t2.ContDir
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
No need for a temp or derived table or a distinct count:
Update t2
set ORG = t1.ORG
from tbl t1
JOIN tbl t2 on t1.ContDir = t2.Contdir and t2.ORG is null
Signature is NULL
-
That was my first thought and then I went and did it the hard way! Go figure! Sigh...
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Yeah, but your way may scale better, as my way causes cross joins with duplicates. Would be interesting to test...Signature is NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply