Confused with aggregates in T-SQL

Question

Ok: This is some of my table structure that matters here

CaseStudyID int
Title nvarchar(50)
OverrideTitle nvarchar(50)

Part of my procedure

Declare @Temp table(CaseStudyID int,
         Title nvarchar(50))

Insert Into @Temp 
SELECT CaseStudyID,Title 
FROM CaseStudy 
WHERE Visible = 1 AND DisplayOnHomePage = 1 
ORDER BY Title

Update @Temp Set Title = TitleOverride 
--Here is where im lost if the title occurs more than once
--I need to replace it with the override
--Schoolboy error or leaking brain I cant get it going


Select * From @Temp

Can anyone help?

Answer 1

You could achieve it without using tempory table this way:

SELECT 
    MIN(CaseStudyID) AS CaseStudyID, 
    CASE WHEN count(*) = 1 THEN 
       MIN(Title) 
    ELSE
       MIN(OverrideTitle) 
    END AS Title
FROM CaseStudy
GROUP BY Title
ORDER BY Title

Answer 2

I'm not sure whether you need every single row of the orginal table. So here is my alternative solution which gives you every row:

SELECT CaseStudyID, Title 
FROM CaseStudy c1
WHERE NOT EXISTS (
   SELECT * FROM CaseStudy c2 
   WHERE c2.CaseStudyID <> c1.CaseStudyID and c2.Title = c1.Title
)

UNION ALL

SELECT CaseStudyID, OverrideTitle
FROM CaseStudy c1
WHERE exists (
   SELECT * FROM CaseStudy c2
   WHERE c2.CaseStudyID <> c1.CaseStudyID and c2.Title = c1.Title
)

ORDER BY Title

Answer 3

That would replace every title that appears multiple times with the corresponding overrideTitle value.

It will keep the first occurrence of it, e.g if you have 3 titles like a,a,a only the second and third one will be replaced

select distinct
    cs1.CaseStudyID,
    case when cs2.CaseStudyID is null then cs1.Title else cs1.overrideTitle end as title
from  
    CaseStudy cs1
    left join CaseStudy cs2 
     on cs1.title = cs2.title
     and cs1.CaseStudyID > cs2.CaseStudyID

If you want to replace all of them just change > to <> as below

select distinct
    cs1.CaseStudyID,
    case when cs2.CaseStudyID is null then cs1.Title else cs1.overrideTitle end as title
from  
    CaseStudy cs1
    left join CaseStudy cs2 
     on cs1.title = cs2.title
     and cs1.CaseStudyID <> cs2.CaseStudyID