问题描述
我有一个包含大约 100 列的旧表(90% 可以为空).在这 90 列中，我想删除所有空字符串并将它们设置为 null.我知道我可以:
I have a legacy table with about 100 columns (90% nullable). In those 90 columns I want to remove all empty strings and set them to null. I know I can:
update table set column = NULL where column = '';
update table set column2 = NULL where column2 = '';
但这很乏味且容易出错.必须有一种方法可以在整个桌子上做到这一点?
But that is tedious and error prone. There has to be a way to do this on the whole table?
推荐答案
UPDATE
TableName
SET
column01 = CASE column01 WHEN '' THEN NULL ELSE column01 END,
column02 = CASE column02 WHEN '' THEN NULL ELSE column02 END,
column03 = CASE column03 WHEN '' THEN NULL ELSE column03 END,
...,
column99 = CASE column99 WHEN '' THEN NULL ELSE column99 END
这仍然是手动执行的，但比您所拥有的痛苦要小一些，因为它不需要您为每一列发送查询.除非您想麻烦编写脚本，否则在执行此类操作时将不得不忍受一定程度的痛苦.
This is still doing it manually, but is slightly less painful than what you have because it doesn't require you to send a query for each and every column. Unless you want to go to the trouble of scripting it, you will have to put up with a certain amount of pain when doing something like this.
添加了 ENDs
这篇关于如何将表中的所有空字符串更改为 NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！