mysql 更新update set from where 语法

其他SQL语法

update set from where

UPDATE sys_role s 
SET s.parent_ids = s1.parent_ids 
from 

    (
    SELECT
        role_id,
    IF
        (
            parent_id = 0,
            CONCAT_WS( ,, parent_id, role_id ),
        CONCAT_WS( ,, 0, parent_id, role_id )) AS parent_ids  
    FROM
        sys_role 
    ) s1 
WHERE
    s1.role_id = s.role_id

Mysql 代替语法 update 表名 inner join 表名 on set

UPDATE sys_role s 
inner  join     (
    SELECT
        role_id,
    IF
        (
            parent_id = 0,
            CONCAT_WS( ,, parent_id, role_id ),
        CONCAT_WS( ,, 0, parent_id, role_id )) AS parent_ids  
    FROM
        sys_role s2
    ) s1   on     s.role_id=s1.role_id
    
    SET s.parent_ids = s1.parent_ids
其他SQL语法 update set from where UPDATE sys_role s SET s.parent_ids = s1.parent_ids from ( SELECT role_id, IF ( parent_id = 0, CONCAT_WS( ,, parent_id, role_id ), CONCAT_WS( ,, 0, parent_id, role_id )) AS parent_ids FROM sys_role ) s1 WHERE s1.role_id = s.role_id Mysql 代替语法 update 表名 inner join 表名 on set UPDATE sys_role s inner join ( SELECT role_id, IF ( parent_id = 0, CONCAT_WS( ,, parent_id, role_id ), CONCAT_WS( ,, 0, parent_id, role_id )) AS parent_ids FROM sys_role s2 ) s1 on s.role_id=s1.role_id SET s.parent_ids = s1.parent_ids
经验分享 程序员 微信小程序 职场和发展