-1

I need to change (mutate) all fields with with value 'N/A' to NULL for all records in a table - is there a quick way to accomplish this ?

user3206440
  • 4,749
  • 15
  • 75
  • 132

1 Answers1

0

You can perform simple UPDATE operation like this.

UPDATE table_name SET string_column = NULL WHERE string_column = 'N/A'

To avoid errors, column must be nullable (i.e don't have 'NOT NULL' ih definition). This is possible to update all columns by one query like this.

UPDATE table_name SET
col1 = CASE
    WHEN col1 = 'N/A' THEN NULL
    ELSE col1
    END,
col2 = CASE
    WHEN col2 = 'N/A' THEN NULL
    ELSE col2
    END
Andrew
  • 1,858
  • 13
  • 15
  • What would the `string_column` be in this case - since I want to do it for all columns ? – user3206440 Apr 16 '16 at 17:04
  • Then you must specify all columns in query explicitly. How many columns and data there are in table? If table is not big, maybe the fastest way will be to perform this query for every column. – Andrew Apr 16 '16 at 17:10
  • Thats the challenge - there are around 150 cols – user3206440 Apr 16 '16 at 17:37
  • Oh, this is big. This is all table columns? Or, maybe, they named like col1, col2 etc and can be iterated? Than it's possible to write some script that will make update query for each column. – Andrew Apr 16 '16 at 17:43
  • 1
    Look at Devolus' answer here http://stackoverflow.com/questions/947215/how-to-get-a-list-of-column-names-on-sqlite3-iphone. That would give you all column names, if correct. Then you can parse an update statement for each column. – I_am_Batman Apr 16 '16 at 17:56
  • In Oracle, I would have just wrote : select 'update '||table_name||' set '||column_name||' = null where '||column_name|| '=''N/A'';' from all_tab_columns where table_name =. This would have given me ready-to-use update queries. Work on something similar. – I_am_Batman Apr 16 '16 at 17:57