If you’re using MySql, and you need to modify multiple columns in one table, you may have come (or will come) across the same problem I ran into the other day. Basically, you can easily modify one column (as found in the documentation):


But what happens when you need to modify multiple columns? I must be a bad Googler because I could not find much talk on the topic. I ran across techonthenet, which talks about multiple modifications, but since I could not get it to work I was thinking it is an Oracle or SqlServer query:

ALTER TABLE supplier
MODIFY (    
    supplier_name   varchar2(100)   not null,
    city    varchar2(75)        

Much frustration later, I randomly tried to format the query by using the keyword MODIFY for each column, like so:

ALTER TABLE supplier 
MODIFY supplier_name varchar(255) NULL,
MODIFY city varchar(255) NULL

As unexpected, it worked! Maybe some SQL experts out there can clear up the details, and confirm that this is the proper way of handling multiple column modifications in MySql. Better yet, point us to some good reference material on the topic.

HTML Form Builder
Ryan Campbell

Modifying Multiple Table Columns by Ryan Campbell

This entry was posted 4 years ago and was filed under Notebooks.
Comments are currently closed.


  1. Anthony Eden · 4 years ago

    Documentation at MySQL’s site provides the guidance:


    ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] …

    note the [,alter_specification]… indicates that you can have as many alter specifications after the alter table as you need.

  2. Richard Harrison · 4 years ago

    Teehee, thanks for making me smile this morning Ryan… did you forget your caffeine fix? :P

  3. Ryan Campbell · 4 years ago

    Well, don’t I feel stupid. But hey, at least now I can properly read MySql documentation. Richard, I’m glad I could help in the laughing department :)

  4. Kim Steinhaug · 4 years ago

    > But hey, at least now I can properly read MySql documentation.

    Nice one, I must admit this little example makes me understand the MySQL syntax better myself, :D