Update using CASE statements on INNER join

Question

I have two tables

1.Project_Cust_Packages__c

---

Project_Id__c | LOV_Name__c |

---

101       |  PAS        |
101       |  PMS        | 
101       |  FTR        |

---

2.AAProj_Tbl

---

 ID | ISPAS |ISPMS |ISFTR |

---

101 |  NULL | NULL | NULL |

---

AND My Query is..

    UPDATE AAProj_Tbl 

SET ISPAS=
CASE 
WHEN A.[LOV_Name__c]='PAS' THEN 'Y'
WHEN ISPAS='Y' THEN 'Y'
ELSE 'N'
END

,ISPMS=
CASE 
WHEN A.[LOV_Name__c]='PMS' THEN 'Y'
WHEN ISPMS='Y' THEN 'Y'
ELSE 'N'
END

,ISFTR=
CASE 
WHEN A.[LOV_Name__c]='FTR' THEN 'Y'
WHEN ISFTR='Y' THEN 'Y'
ELSE 'N'
END 

FROM [Project_Cust_Packages__c] A inner join AAProj_Tbl B
on B.ID=A.[Project_Id__c]

---

Result is

AAProj_Tbl

---

 ID | ISPAS |ISPMS |ISFTR |

---

101 |   N   |  N   | Y    |

---

but result should be

---

 ID | ISPAS |ISPMS |ISFTR |

---

101 |   Y   |  Y   | Y    |

---

Y,Y,Y in all columns because: Project_Cust_Package_c table has project which has FTR,PMS,FTR...

Answer 1

When building queries like this (i.e. updating multiple columns in a single row from multiple rows) you need to make sure that both tables are converted to one-to-one relationship. One way of doing this is to pivot the table A, like this:

UPDATE AAProj_Tbl 

SET ISPAS=
CASE 
WHEN A.[PAS] > 0 THEN 'Y'
ELSE 'N'
END

,ISPMS=
CASE 
WHEN A.[PMS] > 0 THEN 'Y'
ELSE 'N'
END

,ISFTR=
CASE 
WHEN A.[FTR] > 0 THEN 'Y'
ELSE 'N'
END 

FROM 
(SELECT [Project_Id__c], 
SUM(CASE WHEN [LOV_Name__c] = 'PAS' THEN 1 ELSE 0 END) AS [PAS],
SUM(CASE WHEN [LOV_Name__c] = 'PMS' THEN 1 ELSE 0 END) AS [PMS],
SUM(CASE WHEN [LOV_Name__c] = 'FTR' THEN 1 ELSE 0 END) AS [FTR],
FROM [Project_Cust_Packages__c] GROUP BY [Project_Id__c]) AS A
inner join AAProj_Tbl B
on B.ID=A.[Project_Id__c]