mysql 批量添加 更新

    /**
     * 批量添加
     */
    const INNER_UCENTER_USER_BASE_SQL = <<<EOF
        INSERT INTO ucenter_user_base(
           user_name,nice_name,avatar,created,modified
        )
        SELECT
           UcenterImport.card_no,UcenterImport.card_no,"/images/avatar.png",?,?
        FROM
            ucenter_import AS UcenterImport
            LEFT JOIN ucenter_user_base AS UcenterUserBase ON UcenterUserBase.user_name = UcenterImport.card_no
        WHERE
            UcenterUserBase.user_name IS NULL AND UcenterImport.batch_id = ?
EOF;

 

    /**
     * 批量更新
     */
    const UPDATE_UCENTER_IMPORT_SQL = <<<EOF
            UPDATE
                ucenter_import AS UcenterImport
                INNER JOIN ucenter_user_base AS UcenterUserBase ON UcenterUserBase.user_name = UcenterImport.card_no
            SET
                UcenterImport.user_id = UcenterUserBase.user_id
            WHERE
                UcenterImport.batch_id = ?
EOF;

 

UPDATE categories SET 
display_order = CASE id 
        WHEN 1 THEN 3   当id等于1 的时候 display_order 更新为3
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
END, 
title = CASE id 
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3);

　　

　　

UPDATE `member` SET score = (CASE WHEN score IS NOT NULL THEN score = 0 ELSE score = 1 END) where id =1;

　　

 

转载于:https://www.cnblogs.com/sanqiyi/p/7920710.html