请注意我的示例数据、解决方案和注解：

-- 1. CREATE TABLES FOR SAMPLE DATA
DECLARE @transform_tbl TABLE
(
  col1  CHAR(1),
  col2  VARCHAR(20),
  class VARCHAR(100),
  [name] VARCHAR(100),
  flag  BIT,
  INDEX uq_flag CLUSTERED (flag) -- A Unique nonclustered, filtered index would be best
)
DECLARE @lookup1 TABLE (x INT, xx INT, INDEX idx_lu1 CLUSTERED(x)); -- you want an index on x
DECLARE @lookup2 TABLE (x INT, xx VARCHAR(100), INDEX idx_lu2 CLUSTERED(x)); -- you want an index on x

-- 2. POPULATE TABLES WITH SAMPLE DATA
INSERT  @lookup1 VALUES (1,111),(2,222),(3,333),(80,8080);
INSERT  @lookup2 VALUES (12,'sally'),(22,'sue'),(32,'betty'),(802,'lulu');
INSERT @transform_tbl(col1,col2,class)
  VALUES('a','ryan','xxx'),('b','david','xxx'),('c','tan','xxx'),('d','drake','xxx');
INSERT @transform_tbl(col1,col2,class,flag)
  VALUES('x','','xxx',1),('x','','xxx',0),('x','','xxx',1),('x','','xxx',1);

-- 3. SELECT Statement to understand the update
SELECT tf.col1, tf.col2, tf.[name], Class = lu1.XX, [Name] = lu2.XX
FROM @transform_tbl AS tf
CROSS APPLY (VALUES(CASE
    WHEN tf.col1 = 'a' THEN 1
    WHEN tf.col1 = 'b' THEN 2
    WHEN tf.col1 = 'c' THEN 3
    WHEN tf.col1 = 'd' THEN 80 END)) AS f(Class)
CROSS APPLY (VALUES(CASE
    WHEN tf.col2 = 'ryan'  THEN 12
    WHEN tf.col2 = 'david' THEN 22
    WHEN tf.col2 = 'tan'   THEN 32
    WHEN tf.col2 = 'drake' THEN 802 END)) AS f2([Name])
CROSS APPLY (SELECT TOP (1) lu.xx from @lookup1 AS lu where x = f.Class)   AS lu1(XX)
CROSS APPLY (SELECT TOP (1) lu.xx from @lookup2 AS lu where x = f2.[Name]) AS lu2(XX)
WHERE tf.flag IS NULL;

-- 4. Change the SELECT to an UPDATE
;
-- SELECT tf.col1, tf.col2, tf.[name], Class = lu1.XX, [Name] = lu2.XX
UPDATE tf SET Class = lu1.XX, [Name] = lu2.XX
FROM @transform_tbl AS tf
CROSS APPLY (VALUES(CASE
    WHEN tf.col1 = 'a' THEN 1
    WHEN tf.col1 = 'b' THEN 2
    WHEN tf.col1 = 'c' THEN 3
    WHEN tf.col1 = 'd' THEN 80 END)) AS f(Class)
CROSS APPLY (VALUES(CASE
    WHEN tf.col2 = 'ryan'  THEN 12
    WHEN tf.col2 = 'david' THEN 22
    WHEN tf.col2 = 'tan'   THEN 32
    WHEN tf.col2 = 'drake' THEN 802 END)) AS f2([Name])
CROSS APPLY (SELECT TOP (1) lu.xx from @lookup1 AS lu where x = f.Class)   AS lu1(XX)
CROSS APPLY (SELECT TOP (1) lu.xx from @lookup2 AS lu where x = f2.[Name]) AS lu2(XX)
WHERE tf.flag IS NULL

-- 5. Review Results
SELECT tf.col1, tf.[name], tf.class
FROM   @transform_tbl AS tf
WHERE tf.flag IS NULL;

结果（之前）：

col1 col2                 name   Class   Name
---- -------------------- ------ ------- ---------
a    ryan                 NULL   111     sally
b    david                NULL   222     sue
c    tan                  NULL   333     betty
d    drake                NULL   8080    lulu

结果（之后）：

col1 name    class
---- ------- --------
a    sally   111
b    sue     222
c    betty   333
d    lulu    8080

如果您了解apply的工作原理，那么这段代码非常干净，并且易于维护。
注意我使用的索引。生成的执行计划将是所有查找和嵌套循环联接，这是对于这样的查询（使用标准的、常规的方法）所能获得的最佳结果

对于每个记录，一个完整的select将启动它（因此，如果在update表中有1000条记录，您将执行其他1000条select）。优化这个查询的一个简单方法是将select保存到变量中。

declare @a varchar(20), -- I don't know the type..
        @b varchar(20), ..

        @ryan varchar(20), ..

set  @a = select top 1 from lookup_tbl1  where code = 1
set  @b = select top 1 from lookup_tbl1  where code = 1

...

update tf
   SET class
   case
        WHEN tf.column = 'a' THEN @a
        WHEN tf.column = 'b' THEN @b
        WHEN tf.column = 'c' THEN @c
        WHEN tf.column = 'd' THEN @d

   end
   SET name
   case
        WHEN tf.column2 = 'ryan' THEN @ryan
        WHEN tf.column2 = 'david' THEN @david
        WHEN tf.column2 = 'tan' THEN @tan
        WHEN tf.column2 = 'drake' THEN @drake

   end
   SET department
   case
        WHEN tf.column3 like 'd.d%' then 'Director D'
        WHEN tf.column3 like '%AC'  then  'Accounting'

   end

FROM transform_tbl tf WHERE flag is null

希望这对你有帮助