MySQL Update 使用

备忘:

USE `xxx`;
SET SQL_SAFE_UPDATES = 0;

UPDATE `TB_MB_1` T
SET T.`MedicalCount` = 
(
    SELECT S.Total-- ,S.`HospitalID`
    FROM(
            SELECT DISTINCT Base.`HospitalID`
                ,Fix.Total AS FixCount
                ,CASE WHEN Self.Total>0 THEN 1 ELSE 0 END AS SelfCount
                ,CASE WHEN Self.Total>0 THEN Fix.Total+1 ELSE Fix.Total END AS Total
            FROM `TB_MB_2` AS Base
            LEFT JOIN (
                SELECT HospitalID,Total
                FROM(
                    SELECT HospitalID,COUNT(1) AS Total
                    FROM `TB_MB_2`
                    WHERE `STATUS` = 0 AND IsBySelf = 0 AND ServeType = 0
                    GROUP BY HospitalID) AS Temp
                )AS Fix
            ON Base.`HospitalID` = Fix.HospitalID
            LEFT JOIN (
                SELECT HospitalID,Total
                FROM(
                    SELECT HospitalID,COUNT(1) AS Total
                    FROM `TB_MB_2`
                    WHERE `STATUS` = 0 AND IsBySelf = 1
                    GROUP BY HospitalID) AS Temp
                )AS Self
            ON Base.`HospitalID` = Self.HospitalID
    )AS S
    -- ORDER BY S.`HospitalID`
    WHERE T.`MemberID` = S.`HospitalID`
);