if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([user] varchar(2),[E1] varchar(1),[E2] varchar(1),[E3] varchar(1))
insert [ta]
select 'u1','a','b','c' union all
select 'u2','d','e','f'
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([user] varchar(2),[Type] varchar(2),[VOL] varchar(1))
insert [tb]
select 'u1','E1','M' union all
select 'u1','E2','N' union all
select 'u1','E3','P' union all
select 'u2','E1','K' union all
select 'u2','E2','L' union all
select 'u2','E3','P'
go

update b
set b.vol=a.e1
from tb b,
(
select [user],'E1' as type,E1 from ta
union all
select [user],'E2' as type,E2 from ta
union all
select [user],'E3' as type,E3 from ta
) a
where a.type=b.type and a.[user]=b.[user]

select * from tb
/**
user Type VOL
---- ---- ----
u1   E1   a
u1   E2   b
u1   E3   c
u2   E1   d
u2   E2   e
u2   E3   f

(6 行受影响)
**/

------解决方案--------------------
SQL code
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([user] varchar(2),[E1] varchar(1),[E2] varchar(1),[E3] varchar(1))
insert [ta]
select 'u1','a','b','c' union all
select 'u2','d','e','f'
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([user] varchar(2),[Type] varchar(2),[VOL] varchar(1))
insert [tb]
select 'u1','E1','M' union all
select 'u1','E2','N' union all
select 'u1','E3','P' union all
select 'u2','E1','K' union all
select 'u2','E2','L' union all
select 'u2','E3','P';

--使用unpivot和基于连接的更新语句
--需要SQL2005才支持
update b
set b.VOL = a.VOL
from tb b
    join (    select *
            from ta
            unpivot
            (
                Vol for [type]
                in(E1, E2, E3)
            ) pt
        ) a
    on b.[user] = a.[user] and b.Type = a.type;
    
select * from tb;
/*
user Type VOL
---- ---- ----
u1   E1   a
u1   E2   b
u1   E3   c
u2   E1   d
u2   E2   e
u2   E3   f
*/