問題描述
是否可以重構此語句以刪除子查詢？ (Is it possible to refactor this statement to remove the subquery?)
I'm trying to take data from one column, MyTable.SSN, and copy it to another in the same table, MyTable.SSNWithDashes, just formatted differently. If MyTable.SSN doesn't have exactly 9 digits, I don't care to process it at all.
I've tried this:
IF( SELECT LEN( [SSN] ) FROM [MyTable] ) = 9
UPDATE [MyTable] SET [SSNWithDashes] = LEFT( [SSN], 3 ) + '‑' + SUBSTRING( [SSN], 4, 2 ) + '‑' + RIGHT( [SSN], 4 )
ELSE
UPDATE [MyTable] SET [SSNWithDashes] = NULL
While this works, it throws an error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
While I do understand what the warning is saying, I'm not really sure how to go about this differently.
How can I refactor this to remove that warning (and perhaps read a little cleaner)?
‑‑‑‑‑
參考解法
方法 1:
UPDATE dbo.[MyTable]
SET [SSNWithDashes] = CASE
WHEN LEN(SSN) = 9 THEN
LEFT([SSN],3) + '‑' + SUBSTRING([SSN],4,2) + '‑' + RIGHT([SSN],4)
ELSE NULL
END;
方法 2:
Assuming your SSNWithDashes is already NULL then
UPDATE dbo.[MyTable]
SET [SSNWithDashes] = LEFT([SSN],3) + '‑' + SUBSTRING([SSN],4,2) + '‑' + RIGHT([SSN],4)
WHERE LEN(SSN) = 9
Every other rows remain NULL
(by Cypher、Aaron Bertrand、codingbiz)