"John Berman" <[EMAIL PROTECTED]> 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