sql - 将三个 SQL 更新语句合二为一

Question

我可以在一个而不是三个中运行这 3 个更新语句吗？我只能找到有关如何执行此操作的 mysql 示例

update    [NoteAlertLog]
set alertmessage = NULL where alertmessage = ''

update    [NoteAlertLog]
set notes = NULL where cast([notes] as nvarchar(max)) = ''

update    [NoteAlertLog]
set externalnotes = NULL where cast(externalnotes as nvarchar(max)) = ''

我试过这样但得到一个语法错误：

update    [NoteAlertLog]
set alertmessage = NULL where alertmessage = ''
,set notes = NULL where cast([filenotes] as nvarchar(max)) = ''
,set externalnotes = NULL where cast(externalnotes as nvarchar(max)) = ''

我试过没有set：

update    [NoteAlertLog]
set alertmessage = NULL where alertmessage = ''
,notes = NULL where cast([filenotes] as nvarchar(max)) = ''
,externalnotes = NULL where cast(externalnotes as nvarchar(max)) = ''

标签： sqlsql-server-2008tsql

Answer 1

使用case表达式：

update    [NoteAlertLog]
set alertmessage = case when alertmessage = '' then NULL else alertmessage end,
    notes = case when cast([notes] as nvarchar(max)) = '' then NULL else notes end,
    externalnotes = case when cast(externalnotes as nvarchar(max)) = '' then NULL else externalnotes end

where alertmessage = ''
   or cast([notes] as nvarchar(max)) = ''
   or cast(externalnotes as nvarchar(max)) = ''

where可以跳过该子句，但我会保留它以降低事务大小。