MySQL ODBC Connector - Global Replace

Author

Global Replace

John Berman

2005-09-29, 8:23 pm

Hi

We have a database on MySql 4 and it contains many tables. In each field in
the table in the past were there was no data to display we simply left the
field blank, we now want to replace a null entry with No Data

I have no problem doing this on an individual field in each table like so

UPDATE mc_centralgirls SET mc_centralgirls.notes = "No Data"
WHERE (((mc_centralgirls.notes) Is Null));

but it's a big job.

Can I do this at table level across all fields, or ideally at database level
?

Regards

John Berman

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Scott Noyes

2005-09-30, 9:23 am

> We have a database on MySql 4 and it contains many tables. In each field =
in
> the table in the past were there was no data to display we simply left th=
e
> field blank, we now want to replace a null entry with No Data

I advise you to reconsider. You are reducing the flexibility and
usefullness of your data by doing this. Functions designed to take
advantage of NULL values will be lost to your applications. Your
database will consume more disk space, and probably take longer to
search.

Leave the database fields as NULL, and design the output of your
applications to display "No Data" where appropriate.

--
Scott Noyes
snoyes@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sujay Koduri

2005-09-30, 9:23 am

Yes, I don't think you have to do lot of changes to your application to
achieve this. As scott mentioned, always try to keep minimum(whatever is
really useful) data in the DB, either for more performance or for using less
disk space.

sujay

-----Original Message-----
From: Scott Noyes & #91;mailto:snoyes@gm
ail.com]
Sent: Friday, September 30, 2005 6:43 PM
To: JohnBerman@berman.ws
Cc: mysql@lists.mysql.com
Subject: Re: Global Replace

> We have a database on MySql 4 and it contains many tables. In each
> field in the table in the past were there was no data to display we
> simply left the field blank, we now want to replace a null entry with
> No Data

I advise you to reconsider. You are reducing the flexibility and
usefullness of your data by doing this. Functions designed to take
advantage of NULL values will be lost to your applications. Your database
will consume more disk space, and probably take longer to search.

Leave the database fields as NULL, and design the output of your
applications to display "No Data" where appropriate.

--
Scott Noyes
snoyes@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sujayk@andale.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-09-30, 9:23 am

--=_alternative 004AC03B8525708C_=
Content-Type: text/plain; charset="US-ASCII"

"John Berman" <JohnBerman@Berman.ws> wrote on 09/29/2005 06:05:42 PM:

> Hi
>
> We have a database on MySql 4 and it contains many tables. In each field
in
> the table in the past were there was no data to display we simply left
the
> field blank, we now want to replace a null entry with No Data
>
>
> I have no problem doing this on an individual field in each table like
so
>
> UPDATE mc_centralgirls SET mc_centralgirls.notes = "No Data"
> WHERE (((mc_centralgirls.notes) Is Null));
>
>
> but it's a big job.
>
>
> Can I do this at table level across all fields, or ideally at database
level

> ?
>
> Regards
>
> John Berman
>
>

You can't do it on a database level but you can do it to an entire table
at once. You will still need to individually declare which columns need
fixing, though.

option A:
1) Copy the old table's design to a new table. In the new table
redefine any nullable columns you no longer want to be NOT NULL and change
the default value to 'No Data' (or whatever is appropriate for that
column).
2) INSERT all of the records from your old table into the new
table.

INSERT new_tablename (col1, col2, ... , colN)
SELECT col1, col2, ..., colN
FROM old_tablename;

3) Verify the accuracy of your data import. Fix any problems and
repeat until INSERT generates the data you want.
4) use RENAME TABLE to swap the names of the new table and the old
table

RENAME TABLE new_tablename to old_tablename, old_tablename to
new_tablename;

5) use DROP TABLE to get rid of the old data under the new name.

DROP TABLE `new_tablename`;

option B: Use an UPDATE statement combined with the COALESCE() function to
replace all NULL values with the value you want

UPDATE target_table
SET col1 = COALESCE(col1, 'No Data')
, col2 = COALESCE(col2, 'No Data')
, col3 = COALESCE(col3, 'No Data')
, col4 = COALESCE(col4, 'No Data')
....
, colN = COALESCE(colN, 'No Data');

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 004AC03B8525708C_=--

John Berman

2005-09-30, 8:23 pm

Sound advice, I have no done as suggested and my application handles the
Nulls's

Thanks

John B

-----Original Message-----
From: Sujay Koduri & #91;mailto:SujayK@an
dale.com]
Sent: 30 September 2005 14:26
To: Scott Noyes; JohnBerman@berman.ws
Cc: mysql@lists.mysql.com
Subject: RE: Global Replace

Yes, I don't think you have to do lot of changes to your application to
achieve this. As scott mentioned, always try to keep minimum(whatever is
really useful) data in the DB, either for more performance or for using less
disk space.

sujay

-----Original Message-----
From: Scott Noyes & #91;mailto:snoyes@gm
ail.com]
Sent: Friday, September 30, 2005 6:43 PM
To: JohnBerman@berman.ws
Cc: mysql@lists.mysql.com
Subject: Re: Global Replace

> We have a database on MySql 4 and it contains many tables. In each
> field in the table in the past were there was no data to display we
> simply left the field blank, we now want to replace a null entry with
> No Data

I advise you to reconsider. You are reducing the flexibility and
usefullness of your data by doing this. Functions designed to take
advantage of NULL values will be lost to your applications. Your database
will consume more disk space, and probably take longer to search.

Leave the database fields as NULL, and design the output of your
applications to display "No Data" where appropriate.

--
Scott Noyes
snoyes@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sujayk@andale.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...h
gen.org

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.9/115 - Release Date: 29/09/2005

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links