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
You can update multiple columns with one statement by doing something like this:
UPDATE table SET column1='', column2='', column3='' WHERE column1 IS NULL
HOWEVER thsi will only update based on the where clause.
For what you are trying to do, you'll need separate statements.
UPDATE table SET column1='' WHERE column1 IS NULL
UPDATE table SET column2='' WHERE column2 IS NULL
UPDATE table SET column3='' WHERE column3 IS NULL
EDIT Try this:
UPDATE table SET column1= IfNull(column1,''), column2= IfNull(column2,'') , column3= IfNull(column3,'')
You can update a column to itself and check for null there...
UPDATE table SET
column1 = ISNULL(column1,''),
column2 = ISNULL(column2,''),
column3 = ISNULL(column3,'')
etc..
No WHERE clause needed because you want it to run on all records.
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
You can't dynamically create a statement in standard SQL. Without outside help of a programming language, you'll need to repeat this statement for every column.
You need to specify the columns that you want to change. So if your table had four columns, you need multiple queries like this:
UPDATE table SET column1 = '' WHERE column1 IS NULL;
UPDATE table SET column2 = '' WHERE column2 IS NULL;
UPDATE table SET column3 = '' WHERE column3 IS NULL;
UPDATE table SET column4 = '' WHERE column4 IS NULL;
It gets a little easier if you just want to set all columns to an empty string provided that just one of the columns is NULL, but this may not be what you want:
UPDATE table SET column1 = '', column2 = '', column3 = '', column4 = ''
WHERE (column1 IS NULL
OR column2 IS NULL
OR column3 IS NULL
OR column4 IS NULL);
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.
