alter table alter column syntax problem

I am trying to set the charset and collation for all of my database
table columns to
Collation - latin1_general_ci

When I try to execute the following Query (using Php mysql_query()) I
get a syntax error

"ALTER TABLE `table_name` ALTER COLUMN `column_name COLLATE
latin1_general_ci"

I get the following mysql error:

"You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'COLLATE
latin1_general_ci' at line 1"

I can't figure out what the problem is (the column is a varchar).

Using MySQL 4.1.14

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
*****************************

Chuck Anderson wrote:
> I am trying to set the charset and collation for all of my database
> table columns to
> Collation - latin1_general_ci
>
> When I try to execute the following Query (using Php mysql_query()) I
> get a syntax error
>
> "ALTER TABLE `table_name` ALTER COLUMN `column_name COLLATE
> latin1_general_ci"
>
> I get the following mysql error:
>
> "You have an error in your SQL syntax; check the manual that corresponds
> to your MySQL server version for the right syntax to use near 'COLLATE
> latin1_general_ci' at line 1"
>
> I can't figure out what the problem is (the column is a varchar).
>
> Using MySQL 4.1.14
>

The problem is your command is incorrect. Try:

ALTER TABLE `table_name` CHANGE `column_name` COLLATE latin1_bin

If that doesn't work, set all the attributes, i.e.

ALTER TABLE `table_name` CHANGE `column_name` `column_name` VARCHAR(60)
CHARACTER SET latin1 COLLATE latin1_bin NOT NULL

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> Chuck Anderson wrote:
>
>> I am trying to set the charset and collation for all of my database
>> table columns to
>> Collation - latin1_general_ci
>>
>> When I try to execute the following Query (using Php mysql_query()) I
>> get a syntax error
>>
>> "ALTER TABLE `table_name` ALTER COLUMN `column_name COLLATE
>> latin1_general_ci"
>>
>> I get the following mysql error:
>>
>> "You have an error in your SQL syntax; check the manual that corresponds
>> to your MySQL server version for the right syntax to use near 'COLLATE
>> latin1_general_ci' at line 1"
>>
>> I can't figure out what the problem is (the column is a varchar).
>>
>> Using MySQL 4.1.14
>>
>>
>
> The problem is your command is incorrect. Try:
>
> ALTER TABLE `table_name` CHANGE `column_name` COLLATE latin1_bin
>
> If that doesn't work, set all the attributes, i.e.
>
> ALTER TABLE `table_name` CHANGE `column_name` `column_name` VARCHAR(60)
> CHARACTER SET latin1 COLLATE latin1_bin NOT NULL
>
>
Thanks.

CHANGE with all the attributes is the only one that works.

Annnnnnnd now ... reading a little closer (duh) I found CONVERT, which
does exactly what I need to do. ALTER TABLE tbl_name CONVERT TO
CHARACTER SET charset_name;

(For anyone else reading this: There are some caveats, so be sure and
read the manual.)


--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
*****************************

On Mon, 04 Sep 2006 15:56:11 -0600, Chuck Anderson
<websiteaddress@seemy.sig> wrote:

>I am trying to set the charset and collation for all of my database
>table columns to
>Collation - latin1_general_ci
>
>When I try to execute the following Query (using Php mysql_query()) I
>get a syntax error
>
>"ALTER TABLE `table_name` ALTER COLUMN `column_name COLLATE
>latin1_general_ci"
>
>I get the following mysql error:
>
>"You have an error in your SQL syntax; check the manual that corresponds
>to your MySQL server version for the right syntax to use near 'COLLATE
>latin1_general_ci' at line 1"
>
>I can't figure out what the problem is (the column is a varchar).
>
>Using MySQL 4.1.14

Looks like you're missing a backtick after column_name.

Lee

Lee Peedin wrote:
> On Mon, 04 Sep 2006 15:56:11 -0600, Chuck Anderson
> <websiteaddress@seemy.sig> wrote:
>
>
>> I am trying to set the charset and collation for all of my database
>> table columns to
>> Collation - latin1_general_ci
>>
>> When I try to execute the following Query (using Php mysql_query()) I
>> get a syntax error
>>
>> "ALTER TABLE `table_name` ALTER COLUMN `column_name COLLATE
>> latin1_general_ci"
>>
>> I get the following mysql error:
>>
>> "You have an error in your SQL syntax; check the manual that corresponds
>> to your MySQL server version for the right syntax to use near 'COLLATE
>> latin1_general_ci' at line 1"
>>
>> I can't figure out what the problem is (the column is a varchar).
>>
>> Using MySQL 4.1.14
>>
>
> Looks like you're missing a backtick after column_name.
>
> Lee
>
Thanks. That was not the problem, though, ... rather my own sloppy cut
and paste.

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
*****************************