[Solved] SQL Inner join with case when

by


It will not work because you are trying to update one column with multiple values.
i.e. status is one column while * will give you multiple columns.

set status =  (select *

Based on OP’s edit, SQL will be :

UPDATE TCB
SET TCB.STATUS = TE_SUM_O.STATUS
FROM T_CARTON_BOX TCB
INNER JOIN (
            SELECT TE_SUM.CARTON_BOX,(CASE WHEN TE_SUM.REJECT=TE_SUM.TE_REJECT THEN 'R' ELSE 'A' END)STATUS
            FROM(
            SELECT TE.CARTON_BOX,TA.REJECT,SUM(TE.REJECT)TE_REJECT
            FROM T_AQL TA
            INNER JOIN T_ENTRIES TE
            ON TA.AQL_ID = TE.FK_AQL_ID
            GROUP BY TE.CARTON_BOX,TA.REJECT)TE_SUM
            )TE_SUM_O
ON TCB.CARTON_BOX=TE_SUM_O.CARTON_BOX

5

solved SQL Inner join with case when