How to update all columns that are NULL to empty string?

user_398248 0
Viewed 51572

I want to update my table so that every column that has a value of NULL is updated to be and empty string.

Currently I have the following query, but it would only update one column and I want to update all columns that are NULL to empty string.

UPDATE table SET column1='' WHERE column1 IS NULL
sqlite
6 Answers

Actually you can do something like this

DECLARE @sql varchar(max)=''

select @sql= @sql+''+ c.name + '= CASE WHEN ' +c.name+'=''''THEN NULL ELSE ' +c.name+' end,
'
from sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
WHERE t.object_id = 1045578763 -- Your object_id table


PRINT 'UPDATE <TABLE>
        SET '+@sql
user_48918970

This seems impossible, sadly.

For those who wonder why this actually needs to be done, you haven't dealt with linking filemaker to mysql with ODBC.

Long story short, FM "helpfully" coerces all empty strings to null before sending them on to mysql, and applies a non empty constraint when a mysql column has a not null constraint.

This behavior is actually correct for non string data, since empty mean zero for that. But it causes issues for string data.

Since NEW columns keep getting added to the table, it would be useful to be able to update all columns that can take empty strings to have them instead of null without having to know what they all are in advance.

user_115763940
Related