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 |
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...
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]