最近有一個需求
需要把特定產品的合約請假次數歸零
table大概像是這樣

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
+==============+
| OrderDetail  |
+====+=========+
| Id | Product |
+----+---------+
| 1  | A001    |
+----+---------+
| 2  | A001    |
+----+---------+
| 3  | A002    |
+====+=========+

+=============================+
|         PauseDetail         |
+======+=========+============+
| Id   | OrderId | PauseLimit |
+------+---------+------------+
| 1000 | 1       | 10         |
+------+---------+------------+
| 1001 | 2       | 10         |
+------+---------+------------+
| 1002 | 3       | 10         |
+======+=========+============+

原本的想法是用子查詢
把指定產品的合約請假次數撈出來
類似這樣

1
2
3
4
5
6
7
update PauseDetail 
set limit = 0
where P.OrderId in (
	select Id
	from  OrderDetail
	where Product = 'A001'
)

後來突然想到
似乎也可以用join
達到一樣的效果

1
2
3
4
update PauseDetail as P
join OrderDetail as O on P.OrderId = O.Id
set PauseLimit = 0
where O.Product = 'A001'

語法更加簡潔直覺
但不知道為什麼從來沒看過
後來查了一下
原來是只有MySql可以用這個語法
MSSql寫起來反而會有點彆扭

1
2
3
4
5
6
-- MSSQL
UPDATE PauseDetail
   SET PauseLimit = 0
FROM
   PauseDetail AS P JOIN OrderDetail AS O
   ON P.OrderId = O.id AND O.Product = 'A001'

經過這次需求
又多認識了sql一些