sql - 如何使用内部连接和案例语句更新表

Question

问题:

表1

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

---

表2

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

---

结果:用表 1 Like 更新表 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)

我想减去 Like table2(Respective O/N/U/Other) = table2(Respective O/N/U/Other) - table1(Type) 并且更喜欢没有循环的解决方案

我试过了，但没有正常工作 --

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

Answer 1

试试这个

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

出了什么问题:

• 在Update对于联接，使用别名(在我们的例子中为 t2)指定更新表。请参阅 TSQL Update statement 的文档
• Old = Old - [...]行重复 - 我把 Useless = [...]改为行
• CASE语法错误:(CASE <var> WHEN <value> [...] 错误；CASE WHEN <condition> THEN <value> [...] 正确)请参阅 the TSQL CASE statement 的文档