SQL Update: Resolve Subquery Returning Multiple Rows with IN Operator

Update SYSMN.SYS_SERVICE_ITEM_DICT ssi SET ssi.bill_dept = '0' where ssi.ITEM_ID = (SELECT a.clinic_item_id

    FROM mdm.mdm_clinic_item_and_dept a
        left join mdm.mdm_clinic_item ci on ci.CLINIC_DIC_ID = a.CLINIC_ITEM_ID and ci.AUDIT_STATUS = '1'
             left join mdm.sys_dept b
                       on a.dept_id = b.dept_id
                           and b.audit_status = '1'
             left join mdm.sys_hospital_park c
                       on a.corp_code = c.park_id
                           and c.audit_status = '1'
             left join mdm.mdm_standard_dictionary_detail d
                       on a.check_kinds = d.dict_value_id
                           and d.audit_status = '1'
             left join mdm.mdm_standard_dictionary_detail e
                       on a.check_kinds_sub = e.dict_value_id
                           and e.audit_status = '1'
    WHERE  a.audit_status = '1')    **Solution: Using the IN Operator**

When a single subquery returns multiple rows, it can cause errors in your SQL update statement. To resolve this, you can replace the equal (=) operator with the IN operator. The IN operator allows the subquery to return multiple values, and the main query will update all the rows that match those values.

Here's the updated SQL query:

UPDATE SYSMN.SYS_SERVICE_ITEM_DICT ssi
SET ssi.bill_dept = '0'
WHERE ssi.ITEM_ID IN (
    SELECT a.clinic_item_id
    FROM mdm.mdm_clinic_item_and_dept a
    LEFT JOIN mdm.mdm_clinic_item ci ON ci.CLINIC_DIC_ID = a.CLINIC_ITEM_ID AND ci.AUDIT_STATUS = '1'
    LEFT JOIN mdm.sys_dept b ON a.dept_id = b.dept_id AND b.audit_status = '1'
    LEFT JOIN mdm.sys_hospital_park c ON a.corp_code = c.park_id AND c.audit_status = '1'
    LEFT JOIN mdm.mdm_standard_dictionary_detail d ON a.check_kinds = d.dict_value_id AND d.audit_status = '1'
    LEFT JOIN mdm.mdm_standard_dictionary_detail e ON a.check_kinds_sub = e.dict_value_id AND e.audit_status = '1'
    WHERE a.audit_status = '1'
)

By employing the IN operator, the subquery will produce multiple values, and the main query will update all the corresponding rows within the SYSMN.SYS_SERVICE_ITEM_DICT table.