Simplify Update Query

transfield

Hello,
I want to search for the word NULL in any of my fields and replace them with ''(nothing). I'm using the query below to do the job.

Is there any way to summarize all these statements into one query so that I don't have to run them individually?

$query1 = "UPDATE table SET field1 = '' WHERE field1 IS NULL";
$query2 = "UPDATE table SET field2 = '' WHERE field2 IS NULL";
$query3 = "UPDATE table SET field3 = '' WHERE field3 IS NULL";
$query4 = "UPDATE table SET field4 = '' WHERE field4 IS NULL";

Thanks for your advice.

Drabin

NULL = nothing. So, I'm not sure what you are trying to do here. If you are having problems with your query reporting and you keep seeing the word null in your searches, then you have a text "null" instead of a real null in your field. When you create a table for instance:

CREATE TABLE `mc_drop_ragnaros` (
  `id` int(11) NOT NULL auto_increment,
  `boss` varchar(255) default NULL,
  `item` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `slot` varchar(50) default NULL,
  `drop` float(3,0) default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

.. make sure your null's are created like this. This type of NULL means "nothing" is in the field by default. If you have the text version of "NULL" in your fields already then type the following:

$query1 = "UPDATE `table` SET `field1` = NULL WHERE field1 = 'NULL'";

transfield

Thanks for your reply, Drabin.

Actually, these NULLs were mysteriously created when I save any Excel file as a tab delimited text file and upload it into my MySql database. I cannot see the NULLs in my text file otherwise I can do a simple search & replace on it before uploading.

The code I used to create my database is below & I don't think there is anything wrong with it.

$query="CREATE TABLE condo (id int(6) NOT NULL auto_increment,date varchar(10) NOT NULL,area_1 varchar(12) NOT NULL,full_add varchar(255) NOT NULL,status varchar(10) NOT NULL,bu_area int(6) NULL DEFAULT '',price float NULL DEFAULT '',price_psf float NULL DEFAULT '',phone_1 varchar(12) NULL DEFAULT '',phone_2 varchar(12) NULL DEFAULT '',e_num int(6) NULL DEFAULT '',PRIMARY KEY (id))";

By the way, I don't think you have answered my question.

Drabin

I would probably know more if I understood the actual problem. A NULL within a table means empty. You don't have to have "nothing" in there for it to be empty. However, if you are printing a query and you see the following

Condo 12
date 06/10/2006
bu_area NULL

Then what it means is you have a text NULL in your table. Use the query I gave you in the first post to replace the text NULLs with the correct empty NULL value.

transfield

Thank you for your reply, Drabin.

Use the query I gave you in the first post to replace the text NULLs with the correct empty NULL value

This code looks more or less similar to what I have posted in my first post. My original question was

I want to search for the word NULL in any of my fields and replace them with ''(nothing). I'm using the query below to do the job.

Is there any way to summarize all these statements into one query so that I don't have to run them individually?

Crowly

Just a general note. If you want to update several fields in one table using one update command, do this

UPDATE table
  SET field1 = value,
        field2 = value,
        field3 = value;

Edit: This will ofcourse update field1-3 with the set value. So if you add ex "WHERE field1 is NULL and field2 is NULL and field3 is NULL", it will only update the rows where all three fields are NULL, if you use or instead it will update all 3 fields if only one of them is NULL. So it might not always be useful, depends a bit on what you want to do.
As noted in the post below, UNION might be a better solution.

suntra

You may also want to use UNION... have a look at MySQL documention.

transfield

Thanks a lot, Crowly. Much appreciated. Have a good day.