CODEWITHSUNDEEP
mysqlsqlnull
transporter_room_3
transporter_room_3

Reputation: 2633

How do I remove null from table columns?

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.

Upvotes: 2

Views: 4017

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460078

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.

Upvotes: 1

Aman Aggarwal
Aman Aggarwal

Reputation: 18449

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.

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

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.

Upvotes: 0

Related Questions