2

I have a database table populated with 10k rows of data. A great deal of those rows have null values in specific columns. How do I replace this null value with for instance the empty string for a string column?

I'm looking for the easiest way to do this since this operation will only need to be performed once.

What I have tried:

UPDATE tablename set mycolumn='' where mycolumn is null;

This gives me the following error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

I am not the administrator of the database, so I cannot disable safe mode.

UPDATE tablename set mycolumn=''
  where Id in (SELECT Id FROM tablename where mycolumn is null);

This gives me the following error:

Error Code: 1093. You can't specify target table 'tablename' for update in FROM clause.

Note:

In the examples above I have substituted the real tablename and column-name with placeholders.

jarlh
  • 42,561
  • 8
  • 45
  • 63
transporter_room_3
  • 2,583
  • 4
  • 33
  • 51
  • possible duplicate of [Update all SQL NULL values in multiple columns using Column level WHERE clause?](http://stackoverflow.com/questions/3938958/update-all-sql-null-values-in-multiple-columns-using-column-level-where-clause) – Filipe Ferreira Jul 10 '15 at 07:55
  • Do the opposite, replace empty strings with NULL! – jarlh Jul 10 '15 at 07:56
  • 1
    Yes, the requirement is odd. Why do you want to replace `NULL` values with empty stirngs at all? – Tim Schmelter Jul 10 '15 at 07:57
  • possible duplicate of [MySQL error code: 1175 during UPDATE in MySQL Workbench](http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) – Ted Hopp Jul 10 '15 at 08:00
  • @TedHopp: he has mentioned that he can't disable safe mode. – Tim Schmelter Jul 10 '15 at 08:04
  • @FilipeFerreira: that's not a duplicate because OP knows how to update null values with an empty string. The issue is that he can't do that with safe mode and he can't disable safe-mode. – Tim Schmelter Jul 10 '15 at 08:05
  • This seems to be a client, not a server restriction. Did you try to toggle the safe mode option? – Vatev Jul 10 '15 at 08:14
  • @TimSchmelter - Some of the answers there (including the accepted answer) show solutions that do not involve disabling safe mode. – Ted Hopp Jul 10 '15 at 16:13

3 Answers3

1

You could try

UPDATE tablename set mycolumn = '' 
where Id IN (select Id from (select Id from tablename where mycolumn IS NULL) as x)

But why do you want to replace NULL values with empty strings at all?

If you could disable safe mode this would be a duplicate.

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • This worked for me. The reasons for the replacement of null was testing. – transporter_room_3 Jul 10 '15 at 11:41
  • @transporter_room_3: so [Andy's approach](http://stackoverflow.com/a/31335447/284240) didn't work? I was curious, i'm mainly using sql-server so i couldn't test it. – Tim Schmelter Jul 10 '15 at 11:44
  • 1
    No, I get this error: (Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column) which is the same. – transporter_room_3 Jul 10 '15 at 11:50
0

You can alter the table and set NOT NULL for the columns.

And you can also simply do the following:

To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
0

Well, you can do it like:

 UPDATE tablename set mycolumn='' where mycolumn is null and Id is not null;

This actually doesn't change anything in update logic (since ID is primary key and can't be null) and it uses key column in where clause.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71