1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL command to empty a column?

Discussion in 'MySQL' started by Astroman, Jan 1, 2008.

  1. #1
    Does anyone know how to write a command for MYSQL to empty a column?

    For instance I have a table called dogs and a column called tail_length - how do I empty the column tail_length of all its information without deleting the column itself?

    Thanks.
    SEMrush
     
    Astroman, Jan 1, 2008 IP
    SEMrush
  2. Kuldeep1952

    Kuldeep1952 Active Member

    Messages:
    290
    Likes Received:
    18
    Best Answers:
    0
    Trophy Points:
    60
    #2
    Use update statement without a where clause.

    UPDATE dogs set tail_length =''
     
    Kuldeep1952, Jan 1, 2008 IP
  3. RectangleMan

    RectangleMan Notable Member

    Messages:
    2,825
    Likes Received:
    131
    Best Answers:
    0
    Trophy Points:
    210
    #3
    update table_name set column_name="";
    Code (markup):
    His was cut off.
     
    RectangleMan, Jan 3, 2008 IP
  4. Astroman

    Astroman Well-Known Member

    Messages:
    2,359
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #4
    Thanks that's great!

    Is there a command to remove spaces from between words in a single column, or maybe swap spaces for a character like - or _?

    I say spaces because that's what I want to remove, but I guess it's the same as swapping any character - only I don't know how to do it. I've been painstakingly removing unwanted spaces in a column and it just occurred to me that there must be an SQL query that will do the same thing all at once?

    Or I guess if there was a way to export a single column I could just use a text editor then import the column back in, only I don't know how to export single columns either...
     
    Astroman, Jan 5, 2008 IP
  5. kmap

    kmap Well-Known Member

    Messages:
    2,215
    Likes Received:
    29
    Best Answers:
    2
    Trophy Points:
    135
    #5
    you can write a php program to do this

    1. select the coloumn
    2. cycle through each record in coloumn one by one
    3. use replace function to replace space between chars by ''
    4. update the record

    Regards

    Alex
     
    kmap, Jan 5, 2008 IP
  6. Astroman

    Astroman Well-Known Member

    Messages:
    2,359
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #6
    How would that look in PHP?

    I've nearly done it with PHPMyadmin by deleting the columns I don't want, then saving the table so I only have the column I do want, then dropping the table and importing it back in again.

    Now I have emptied the column I want to change and I have edited the column file I made above in Notepad, so I'm nearly there... but I can't figure out how to import the column data I made.

    I've tried this:

    INSERT INTO `DOGS_CATEGORY` (DOG_URL) VALUES('Springer_Spaniel');

    DOG_URL is the column I'm trying to change, but the above query messes up other columns too, so I must have done something wrong?
     
    Astroman, Jan 5, 2008 IP
  7. rkquest

    rkquest Well-Known Member

    Messages:
    829
    Likes Received:
    23
    Best Answers:
    0
    Trophy Points:
    140
    #7
    If you want to remove the spaces in all the values of a certain column in your table, you can use this query:

    UPDATE table_name SET column_name = REPLACE(column_name, ' ', '');
    Code (markup):
    Now, you can use this same query to swap a character say dash (-) or underscore (_) to replace the spaces. It would look like this:

    use dash instead of space:
    UPDATE table_name SET column_name = REPLACE(column_name, ' ', '-');
    Code (markup):
    use underscore instead of space:
    UPDATE table_name SET column_name = REPLACE(column_name, ' ', '_');
    Code (markup):
    Hope that helps.
     
    rkquest, Jan 11, 2008 IP
    Astroman likes this.
  8. Astroman

    Astroman Well-Known Member

    Messages:
    2,359
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #8
    Thanks, that's great. :)
     
    Astroman, Jan 11, 2008 IP
  9. LittleJonSupportSite

    LittleJonSupportSite Peon

    Messages:
    386
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    0
    #9
    I know you want to clear the contents but I highly recommend using a standard value.

    NULL is really not valid for a database like that. I would do an update to like NA or N/A or 0.

    There is valid logic behind this.

    Let's use this simple example:

    Table1 has 3 fields id,x,y

    Table contains 3 records as such:

    1, Jon, Test
    2, You,
    3, Him, Test

    
    select count(id) from table1;
    
    Code (markup):

    Will yield 3 records.

    
    select count(y) from table1;
    
    Code (markup):
    Will yield 2 records.



    It complete ignores the fact that the other columns contain data because you cannot count NULL.

    This same rule will apply when using > <.

    It's your database and you do as you feel but my suggestion is not to carry null values in the database.
     
    LittleJonSupportSite, Jan 11, 2008 IP
  10. Astroman

    Astroman Well-Known Member

    Messages:
    2,359
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #10
    I'm not sure what you mean, there are no null figures?
     
    Astroman, Jan 11, 2008 IP
  11. LittleJonSupportSite

    LittleJonSupportSite Peon

    Messages:
    386
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    0
    #11
    The previous response before mine stated

    
    update something set field='';
    
    Code (markup):
    That would create a null value.

    I was only giving you my opinion. You do not have to ad hear to anything I say.

    I have just been doing this a while and things like that can get you later on down the road.
     
    LittleJonSupportSite, Jan 11, 2008 IP
  12. Astroman

    Astroman Well-Known Member

    Messages:
    2,359
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #12
    I'm not saying you're wrong, just that I don't understand what you mean. The column still has stuff in is, I just didn't want any spaces between the words.
     
    Astroman, Jan 11, 2008 IP
  13. LittleJonSupportSite

    LittleJonSupportSite Peon

    Messages:
    386
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    0
    #13
    AHHHHH. Ok SO you really want to do a str_replace and not so much an update.

    You want to str_replace " " with ""

    I follow now.

    The previous poster had it update to ""

    Which means it wipes the field data.
     
    LittleJonSupportSite, Jan 11, 2008 IP
  14. Astroman

    Astroman Well-Known Member

    Messages:
    2,359
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #14
    This is what I actually used in the end I think:

    SELECT `tail_length`. replace(tail_length, ' ', '') from DOGS
     
    Astroman, Jan 11, 2008 IP
  15. highborn

    highborn Active Member

    Messages:
    184
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    55
    #15
    give me your DB and I will do it for you. :)
     
    highborn, Jan 12, 2008 IP
  16. Astroman

    Astroman Well-Known Member

    Messages:
    2,359
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    135
    #16
    Do you have difficulty with reading?
     
    Astroman, Jan 12, 2008 IP