求一条链表更新的sql语句

表Emp_Tracking
AccountNum  SubAccount  AccountType LastPackagePurchaseDate LastPackageExpireDate   LastDateWithoutAmount   LastDateWithAmount
----------- ----------- ----------- ----------------------- ----------------------- ----------------------- -----------------------
100         0           M           2011-12-01 00:00:00.000 2011-12-09 20:54:57.113 NULL                    NULL
100         1           M           2011-12-20 00:00:00.000 2011-12-09 20:54:57.113 NULL                    NULL
100         2           M           2011-10-07 00:00:00.000 2011-12-09 20:54:57.113 NULL                    NULL
100         3           M           NULL                    2011-12-09 20:54:57.113 NULL                    NULL
100         4           M           NULL                    2011-12-09 20:54:57.113 NULL                    NULL
100         5           M           NULL                    2011-12-09 20:54:57.113 NULL                    NULL
101         0           M           2011-12-03 00:00:00.000 2011-12-09 20:54:57.113 NULL                    NULL
101         1           M           2011-12-10 00:00:00.000 2011-12-09 20:54:57.113 NULL                    NULL
101         2           M           2011-12-03 00:00:00.000 2011-12-09 20:54:57.113 NULL                    NULL
101         3           M           NULL                    2011-12-09 20:54:57.113 NULL                    NULL

表Billing_SalesInvoice_Master
InvoiceId   AccountNum  SubAccount  AccountType PurchaseDate            InvoiceAmount
----------- ----------- ----------- ----------- ----------------------- ---------------------
1           100         0           M           2011-12-01 00:00:00.000 200.00
2           100         1           M           2011-12-01 00:00:00.000 400.00
3           100         1           M           2011-12-05 00:00:00.000 600.00
4           100         2           M           2011-10-07 00:00:00.000 100.00
5           101         0           M           2011-12-03 00:00:00.000 0.00
6           101         1           M           2011-12-10 00:00:00.000 0.00
7           101         1           M           2011-12-01 00:00:00.000 200.00
8           101         2           M           2011-12-03 00:00:00.000 0.00
9           101         2           M           2011-12-10 00:00:00.000 0.00
10          101         0           M           2011-12-15 00:00:00.000 0.00
11          100         2           M           2011-10-09 00:00:00.000 100.00

我要通过AccountNum,SubAccount,AccountType连接更新Emp_Tracking表中的字段LastPackageExpireDate 的值，
当在Emp_Tracking表中有而在表Billing_SalesInvoice_Master没有的话，则把LastPackageExpireDate值设置为Null,
如：100 - 3 - M这条记录的LastPackageExpireDate值设置为Null

4 个解决方案

#1

update
   a
set
   LastPackageExpireDate=b.LastPackageExpireDate
from
  Emp_Tracking a left join Billing_SalesInvoice_Master
on
  a.AccountNum=b.AccountNum and a.SubAccount=b.SubAccount and a.AccountType=b.AccountType

#2

update
   a
set
   LastPackageExpireDate=b.PurchaseDate
from
  Emp_Tracking a left join Billing_SalesInvoice_Master b
on
  a.AccountNum=b.AccountNum and a.SubAccount=b.SubAccount and a.AccountType=b.AccountType

#3

引用 2 楼 fredrickhu 的回复:

SQL code
update
   a
set
   LastPackageExpireDate=b.PurchaseDate
from
  Emp_Tracking a left join Billing_SalesInvoice_Master b
on
  a.AccountNum=b.AccountNum and a.SubAccount=b.SubAccount and a.Acco……

估计你还没弄懂我的意思吧，你这样是不行的，如果这么简单我就不会来这里发帖了……

#4

引用楼主 yangchun1213 的回复:

当在Emp_Tracking表中有而在表Billing_SalesInvoice_Master没有的话，

这句话没看懂,能否再多给两条结果