sql - Sql server 未在查询中使用嵌套 case 语句更新记录
问题描述
我正在使用 sql server 2012,这是我的查询:
update tablename set column1=case
when column2 is null or column2='' then '1st'
when column3 like 'Information%' and
(DATEDIFF(YEAR,convert(datetime,column6,103),getdate())) not IN (18,19,20,21) then '2nd a'
when column3 not like 'Information%' and (DATEDIFF(YEAR,convert(datetime,column6,103),getdate()))
not IN (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40) then '2nd b'
when column4 not like '%[^0-9]%' and
CAST(replace(replace(column5,',',''),'','0') as bigint)
< 10*CAST(CAST((replace(ISNULL(REPLACE(column5,'','0'),'0'),',','')) as float) as bigint)/100 and column12='1'
then '3rd a'
when column4 not like '%[^0-9]%' and
CAST(replace(replace(column5,',',''),'','0') as bigint)
< 20*CAST(CAST((replace(ISNULL(REPLACE(column5,'','0'),'0'),',','')) as float) as bigint)/100 and column12='2' or column12='3'
then '3rd b'
when UPPER(column20)='YES' or column20='Yes' or column20='Unknown' then '4th'
when column15<>'' and column15 not in (select column2 from table2) and
CAST((replace(ISNULL(column15,'0'),',','')) as int) <1000000
then '5th'
else null
end
,
column2=
case
when column1='1st' then 'Rejected Reason'
when column1='2nd a' then 'Rejected Reason'
when column1='2nd b' then 'Rejected Reason'
when column1='3rd a' then 'Rejected Reason'
when column1='3rd b' then 'Rejected Reason'
when column1='4th' then 'Rejected Reason'
when column1='5th' then 'Rejected Reason'
else null end , column10=1 where column11=null
它不是更新我的表只是结果
(0 row(s) affected)
即使我尝试了一个查询,也无法配置问题
update tablename set column1=case
when column2 is null or column2='' then '1st'
else null
end
where column11=null
有什么我做错了吗?或任何其他方式来达到我想要的结果?
解决方案
where column11=null
必须写 WHERE Column11 IS NULL
推荐阅读
- r - 使用 ts() 函数在 R 中具有 1 分钟频率的时间序列
- vba - 通过 Windows 任务运行 Excel 宏并保存文件
- python - 使用布尔数组进行索引
- python - 为什么一个新的 Python 变量的引用计数为 108?
- php - 关于 HTML 和 PHP 文件之间区别的愚蠢问题
- python - Numpy.fromfunction():如何将它与字符串一起使用?
- batch-file - 如何使用其标签访问 USB 驱动器并使用 CMD 导航
- html - 如何使用 CSS 使标题的大小有条件地增长?
- sql - How to read column from csv and run a update query?
- reactjs - React - Can't use hooks in custom hooks