简洁的SQL--一条语句更新从属账号

更新从属帐号
现有表info_company

IF EXISTS (SELECT * FROM sysobjects WHERE [name]='info_company')
DROP TABLE info_company
BEGIN
CREATE TABLE info_company(
    company_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    com_name nvarchar(120) NOT NULL,
    conn_man NVARCHAR(15) NULL,                
    conn_Tel VARCHAR(16) NULL,                
    com_email VARCHAR(64) NULL,
    com_create_time datetime NOT NULL,
    is_famous BIT  DEFAULT(0) NOT NULL,    
    com_level INT DEFAULT(1) NOT NULL ,                
    com_remark nvarchar(120) NULL
)
END

--------插入测试信息-------------------
BEGIN
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('神州数码','Mr.Liu','2012-9-20',1,5)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('重庆人才网','Mr.Li','2012-9-20',1,4)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('梦幻天方','Mr.Li','2012-9-20',1,3)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('平安保险','Mr.Li','2012-9-20',1,1)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('尚观科技','Mr.Li','2012-9-20',0,1)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('用友软件','Mr.Li','2012-9-20',0,1)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('金蝶软件','Mr.Li','2012-9-20',0,2)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('深天之星软件','Mr.Li','2012-9-20',0,3)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('上海小兵科技','Mr.Li','2012-9-20',1,5)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('百度','Mr.Li','2012-9-20',1,4)
    
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('神州数码1','Mr.Liu','2012-9-20',1,5)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('重庆人才网2','Mr.Li','2012-9-20',1,4)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('梦幻天方3','Mr.Li','2012-9-20',1,3)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('平安保险4','Mr.Li','2012-9-20',1,1)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('尚观科技5','Mr.Li','2012-9-20',0,1)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('用友软件6','Mr.Li','2012-9-20',0,1)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('金蝶软件7','Mr.Li','2012-9-20',0,2)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('深天之星软件8','Mr.Li','2012-9-20',0,3)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('上海小兵科技9','Mr.Li','2012-9-20',1,5)
    INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('百度10','Mr.Li','2012-9-20',1,4)
END
GO

　　需求：在表ifno_company中可能有一些这样的帐号
ABCD,CD两个帐号的主帐号是B，B的主帐号是A
EFG，G的主帐号是F，F的主帐号是E

需要将CD的主帐号更新为A，G的主帐号更新为E
请写一个通用的sql语句进行更新。

分析：这个题最难的还是找到父账号，如果使用普通的 SQL来写，很难完成，一次想到了才有Case when then end 语句来作为条件判断。

代码如下：

UPDATE  ic
SET ic.link_com_id = CASE WHEN ic2.link_com_id  IS NULL THEN ic2.company_id ELSE ic2.link_com_id END 
FROM info_company ic
LEFT JOIN info_company ic2  ON ic2.company_id=ic.link_com_id
WHERE ic.link_com_id IS NOT NULL 

非常简洁的写法，可是性能上面损失的却多了点，不知道是福可以优化下？