June 24, 2009 at 4:05 am
I need to input data into a column in a table using a reference table. Below is code displaying the scenario.
-- REFERENCE TABLE
CREATE TABLE #REF
(
Specialty varchar(20)
,Team varchar(20)
,Code varchar(20)
)
INSERT INTO #REF
SELECT 'POD', 'HEALTH', 'SS18' UNION ALL
SELECT 'POD', 'HEART', 'SS18' UNION ALL
SELECT 'POD', 'DEFAULT', 'SS19'
-- MAIN TABLE
CREATE TABLE #SpecailtyTeam
(
Specialty varchar(20)
,SpecailtyDescription varchar(20)
,Team varchar(20)
,Code varchar(20)
)
INSERT INTO #SpecailtyTeam
SELECT 'POD', 'PODIATRY', 'HEALTH', NULL UNION ALL
SELECT 'POD', 'PODIATRY', 'HEART', NULL UNION ALL
SELECT 'POD', 'PODIATRY', 'LEG', NULL UNION ALL
SELECT 'POD', 'PODIATRY', 'NULL', NULL
I want to update the Code column in the #SpecailtyTeam table with the code from the #REF table where Specialty and Team = the Specialty and Team columns in the #Ref table. This is straight forward enough.
However the problem I have is how to update the code column if the Team value from #SpecailtyTeam is null or not found in the #REF table. In this case it should take the code where the Specialty value is the same but the team is called DEFAULT.
For example. in the situation above in #SpecailtyTeam table the rows where the team = LEG and is null should be updated with the code SS19 as these teams are not found in the #REF table.
How can I do this?
June 24, 2009 at 4:23 am
Hi
Thanks for the sample data. 🙂
Try this:
UPDATE st SET
st.Code = ISNULL(r1.Code, r2.Code)
FROM #SpecailtyTeam st
JOIN #REF r2 ON r2.Team = 'DEFAULT'
LEFT JOIN #REF r1 ON st.Team = r1.Team
Flo
June 24, 2009 at 5:52 am
Cheers!! Much appreciated
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply