如何使用内连接和 Case 语句更新表

问题 ：

Table1

CatId - -   Type - -    Qty
==============================
8        ||    O   ||   10
8        ||    N   ||   20
8        ||    U   ||   30
30       ||    N   ||   5
30       ||    O   ||   15
30       || NULL   ||   25

Table2

catId -- Old -    -New -- Useless -- Other
========================================
8      || 100   || 70   ||  140      || 110
30     || 10    || 20   ||  30       || 50

结果：用表 1 更新表 2

-------------------------------------------------
catId --   Old --   New -- Useless -- Other
8       || 90    || 50  ||  110     || 110
30      ||  5    ||  5  ||  30      || 25

结果应该如何：

表1和表2有一个共同的列CatId。

Column of table 1 Type is connects with Table2  
    AS  (Old - O / New - N / Useless - U / Other - NULL)

我想要减去像 table2(各自的 O/N/U/其他) = table2(各自的 O/N/U/其他) - table1(类型) 并且更喜欢没有循环的解决方案

我尝试过这个但不能正常工作--

Update Table2
Set New = New - (CASE Type WHEN 'N' THEN (Table1.qty) Else 0 End),
    Old = Old  - (CASE Type WHEN 'O' THEN (Table1.qty) Else 0 End),
    Old = Old  - (CASE Type WHEN 'O' THEN (Table1.qty) Else 0 End),
    Other= Othere- (CASE Type WHEN is Null THEN (Table1.qty) Else 0 End)
from table1 
inner join table2 
On table1.catId = table2 .catId

Try this

Update t2
Set New = New - (CASE WHEN type='N' THEN (t1.qty) Else 0 End),
    Old = Old  - (CASE WHEN type='O' THEN (t1.qty) Else 0 End),
    Useless = Useless  - (CASE WHEN type='U' THEN (t1.qty) Else 0 End),
    Other= Other - (CASE WHEN type is Null THEN (t1.qty) Else 0 End)
from Table1 t1
inner join Table2 t2
On t1.catId = t2.catId

什么问题：

• in an Update对于连接，使用别名指定更新表（t2在我们的例子中）。请参阅文档TSQL更新语句 http://msdn.microsoft.com/en-us/library/ms177523.aspx
• the Old = Old - [...]行重复 - 我把Useless = [...]行代替
• the CASE语法错误：(CASE <var> WHEN <value> [...]是错的;CASE WHEN <condition> THEN <value> [...]是正确的）请参阅文档TSQL CASE 语句 http://msdn.microsoft.com/en-us/library/ms181765.aspx