View Full Version : Resolved Remove column data without removing column

07-20-2011, 08:53 PM
Hi guys,

I know about the drop column mysql command, but I understand that removed the column from the database.

Is there a way to delete all the fields in 1 column, but do not delete the column itself?

07-20-2011, 10:45 PM
Yes update the column to be '' (empty).

Old Pedant
07-21-2011, 04:56 AM
Ummm...if the column is not a text column of some kind (that is, if it is a number or datetime, say) then you can *NOT* update the column to be ''

A more general answer is to update the column to NULL.

That is:

UPDATE tablename SET columnname = NULL


But even that can fail if the column was specified as NOT NULL.

So there's no 100% general answer. It depends on the type of column and on whether the column allows NULLs or not.

If you do

DESCRIBE tablename

and show us what it shows you and tell us what column you wish to remove the data from, we can tell you what will (and won't) work.

07-21-2011, 04:27 PM
Thanks for the help guys. My original idea was to erase the whole column and then populate it again with the updated information. Instead I just loop through each row and run an UPDATE query.

Thanks for your help!

Old Pedant
07-21-2011, 10:57 PM
You do *NOT* need or want to "loop through each row"!!!

That is horribly slow and clumsy!

If you do what I showed you

UPDATE tablename SET columnname = NULL

that will do *ALL ROWS* without any need for an ugly loop!!!!

07-22-2011, 02:58 PM
While I agree a loop is probably not necessary (unless you have exotic conditions you need to evaluate), I take issue with the idea that loops are ugly.

Loops.... are.... beautiful.



Old Pedant
07-23-2011, 05:32 AM
Now wait a minute!

Your location says "Utah", but that sure as heck isn't Utah!

And how many times did you stop at the Alpine Inn when you rode that loop?