基于另外两个表的更新表

Question

我有3张桌子，客户，customerCategory，出席情况如下：

客户：

CustomerId | CustomerCategory | Name

A1         | 2                | User1
B1         | 1                | User2
C1         | 3                | User3

CustomerCategory

CustCategoryId | CategoryName | StartTime | EndTime
1              | Category1    | 8:15      | 17:15
2              | Category2    | 7.30      | 17:30
3              | Category3    | 8.15      | 15:15

考勤

Id | CustomerId | InTime | OutTime
1  | A1         | 7:30   | 17:30
2  | A1         | 7:30   | NULL
3  | B1         | 8.15   | NULL
4  | C1         | 8:10   | NULL

我希望从Outtime为NULL的CustomerCategory表中将考勤表Attendane.Outime列更新为相关的Attendane.Outime。我希望我能写一个合并查询，但我很困惑。还有其他方法可以更新允许表来设置相关的OutTime吗？

Answer 1

UPDATE Attendance
SET Attendance.OutTime = CustomerCategory.EndTime
FROM Attendance
INNER JOIN Customer
ON (Attendance.CustomerID = Customer.CustomerID)
INNER JOIN CustomerCategory
ON (Customer.CustomerCategory = CustomerCategory.CustCategoryId)
WHERE Attendance.OutTime IS NULL;

您可能需要使用一些语法，因为我已经有一段时间没有处理MS了，但是基本思想如下所示。如果你有什么困难，请告诉我。

Answer 2

使用Merge尝试以下操作：

Declare @Customer table (CustomerId varchar(5), CustomerCategory int, Name varchar(10))

insert into @CUSTOMER 
select 'A1', 2, 'User1' union
select 'B1', 1, 'User2' union
select 'C1', 3, 'User3'

Declare @CustomerCategory TABLE (CustCategoryId INT, CategoryName varchar(10), StartTime time, EndTime time)
insert into @CustomerCategory
select 1, 'Category1', '8:15', '17:15' union
select 2, 'Category2', '7:30', '17:30' union
select 3, 'Category3', '8:15', '15:15'

Declare @Attendance table (Id int, CustomerId varchar(5), InTime time, OutTime time)
insert into @Attendance
select 1, 'A1', '7:30', '17:30' union
select 2, 'A1', '7:30', NULL union
select 3, 'B1', '8:15', NULL union
select 4, 'C1', '7:30', NULL

select * from @Customer
select * from @CustomerCategory
select * from @Attendance

merge @Attendance a
using 
(select c.CustomerId, cc.EndTime from @Customer c
    join @CustomerCategory cc on c.CustomerCategory = cc.CustCategoryId)x
on x.CustomerId = a.CustomerId
and a.OutTime is null
when matched then update
set a.OutTime = x.EndTime ;

select * from @Attendance

哈哈！