alter table modify syntax error
Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql> describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql> alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): 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 'after model' at line 1 I'm just wondering what I'm doing wrong here, because the syntax looks correct to me! Thanks -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql> describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql> alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): 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 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
Cool guys, that did it.. ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model; For some reason the book I'm following doesn't specify that you have to note the data type in moves! This helped. and thanks again. Tim On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen wrote: > On 28-06-2014 19:11, Tim Dunphy wrote: > >> Hello, >> >> I'm trying to use a very basic alter table command to position a column >> after another column. >> >> This is the table as it exists now: >> >> mysql> describe car_table; >> +-+--+--+-+-++ >> | Field | Type | Null | Key | Default | Extra | >> +-+--+--+-+-++ >> | car_id | int(11) | NO | PRI | NULL | auto_increment | >> | vin | varchar(17) | YES | | NULL | | >> | color | varchar(10) | YES | | NULL | | >> | year | decimal(4,0) | YES | | NULL | | >> | make | varchar(10) | YES | | NULL | | >> | model | varchar(20) | YES | | NULL | | >> | howmuch | decimal(5,2) | YES | | NULL | | >> +-+--+--+-+-++ >> 7 rows in set (0.03 sec) >> >> I am trying to position the 'color' column after the 'model' column with >> the following command: >> >> mysql> alter table car_table modify column color after model; >> >> And I'm getting the following error: >> >> ERROR 1064 (42000): 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 'after model' at line 1 >> > > Try: > alter table car_table modify column color varchar(10) after model; > > / Carsten > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql> alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): 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 'column color varchar(10) sixth' at line 1 Here's my table one more time for reference: mysql> describe car_table; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | car_id | int(11) | NO | PRI | NULL| auto_increment | | vin| varchar(17) | YES | | NULL|| | year | decimal(4,0) | YES | | NULL|| | make | varchar(10) | YES | | NULL|| | model | varchar(20) | YES | | NULL|| | color | varchar(10) | YES | | NULL|| | price | decimal(7,2) | YES | | NULL|| ++--+--+-+-++ 7 rows in set (0.01 sec) I appreciate your suggestions so far and it would be great if I could get some help with this one too. Thanks Tim On Sat, Jun 28, 2014 at 1:34 PM, Tim Dunphy wrote: > Cool guys, that did it.. > > ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model; > > For some reason the book I'm following doesn't specify that you have to > note the data type in moves! This helped. and thanks again. > > Tim > > > On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen > wrote: > >> On 28-06-2014 19:11, Tim Dunphy wrote: >> >>> Hello, >>> >>> I'm trying to use a very basic alter table command to position a column >>> after another column. >>> >>> This is the table as it exists now: >>> >>> mysql> describe car_table; >>> +-+--+--+-+-++ >>> | Field | Type | Null | Key | Default | Extra | >>> +-+--+--+-+-++ >>> | car_id | int(11) | NO | PRI | NULL | auto_increment | >>> | vin | varchar(17) | YES | | NULL | | >>> | color | varchar(10) | YES | | NULL | | >>> | year | decimal(4,0) | YES | | NULL | | >>> | make | varchar(10) | YES | | NULL | | >>> | model | varchar(20) | YES | | NULL | | >>> | howmuch | decimal(5,2) | YES | | NULL | | >>> +-+--+--+-+-++ >>> 7 rows in set (0.03 sec) >>> >>> I am trying to position the 'color' column after the 'model' column with >>> the following command: >>> >>> mysql> alter table car_table modify column color after model; >>> >>> And I'm getting the following error: >>> >>> ERROR 1064 (42000): 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 'after model' at line 1 >>> >> >> Try: >> alter table car_table modify column color varchar(10) after model; >> >> / Carsten >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql >> >> > > > -- > GPG me!! > > gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B > > -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
RE: alter table modify syntax error
Hi Tim, > -Original Message- > From: Tim Dunphy [mailto:bluethu...@gmail.com] > Sent: Sunday, 29 June 2014 03:45 > Cc: mysql@lists.mysql.com > Subject: Re: alter table modify syntax error > > Hey guys, > > Sorry to hit you with one more. But I'm trying to use a positional statement > in a column move based on what you all just taught me: > > mysql> alter table modify column color varchar(10) sixth; > > But I am getting this error: > > ERROR 1064 (42000): 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 'column color varchar(10) sixth' at line 1 The syntax "sixth" is not a supported syntax. You should use the syntax "AFTER " where you replace with the column name you want to position the modified column after. See also: https://dev.mysql.com/doc/refman/5.5/en/alter-table.html Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
> > The syntax "sixth" is not a supported syntax. You should use the syntax > "AFTER " where you replace with the column name > you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in-mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Thanks Tim On Sat, Jun 28, 2014 at 7:46 PM, Jesper Wisborg Krogh wrote: > Hi Tim, > > > -Original Message- > > From: Tim Dunphy [mailto:bluethu...@gmail.com] > > Sent: Sunday, 29 June 2014 03:45 > > Cc: mysql@lists.mysql.com > > Subject: Re: alter table modify syntax error > > > > Hey guys, > > > > Sorry to hit you with one more. But I'm trying to use a positional > statement > > in a column move based on what you all just taught me: > > > > mysql> alter table modify column color varchar(10) sixth; > > > > But I am getting this error: > > > > ERROR 1064 (42000): 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 'column color varchar(10) sixth' at line 1 > > The syntax "sixth" is not a supported syntax. You should use the syntax > "AFTER " where you replace with the column name > you want to position the modified column after. > > See also: https://dev.mysql.com/doc/refman/5.5/en/alter-table.html > > Best regards, > Jesper Krogh > MySQL Support > > > -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
RE: alter table modify syntax error
Hi Tim, > -Original Message- > From: Tim Dunphy [mailto:bluethu...@gmail.com] > Sent: Sunday, 29 June 2014 10:09 > To: Jesper Wisborg Krogh > Cc: mysql@lists.mysql.com > Subject: Re: alter table modify syntax error > > > > > The syntax "sixth" is not a supported syntax. You should use the > > syntax "AFTER " where you replace with > the > > column name you want to position the modified column after. > > > Oh thanks. That's actually what I ended up doing after I got frustrated with > that error. I was following the book 'Head First SQL' which was suggesting > that you could do something like what this user was trying in this stack > overflow thread: > > http://stackoverflow.com/questions/19175240/re-arranging-columns-in- > mysql-using-position-keywords-such-as-first-second > > But the answer in that thread too suggests that this is wrong. So is the Head > First SQL book just referring to an outdated syntax that doesn't work > anymore? I can't imagine that it never worked if it's in that book. But hey ya > never know! ;) Given the title of the book is "Head First SQL" and not "Head First MySQL" it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
> > Given the title of the book is "Head First SQL" and not "Head First MySQL" > it probably isn't exclusively using syntax for MySQL. While SQL is a > standard the various SQL databases are not completely identical with the > syntax they support. This may be due to not completely conforming to the > standard, using different versions of the SQL standard, or that there is > not standard for that operation. Hey, that's some good input. Thanks and makes total sense. I guess the reason I thought I could use that syntax is that the book uses MySQL for all it's examples and explains that it does so because MySQL is a free and open source version of SQL that's easy to install. But maybe you're right and they do depart into other syntaxes of SQL. I just don't know where they got that 'first, second, third, etc' version of the alter table syntax from. Definitely not sweatin' this detail tho, I am totally fine with what you showed me that works. Thanks again for your input! Tim On Sat, Jun 28, 2014 at 9:14 PM, Jesper Wisborg Krogh wrote: > Hi Tim, > > > -Original Message- > > From: Tim Dunphy [mailto:bluethu...@gmail.com] > > Sent: Sunday, 29 June 2014 10:09 > > To: Jesper Wisborg Krogh > > Cc: mysql@lists.mysql.com > > Subject: Re: alter table modify syntax error > > > > > > > > The syntax "sixth" is not a supported syntax. You should use the > > > syntax "AFTER " where you replace with > > the > > > column name you want to position the modified column after. > > > > > > Oh thanks. That's actually what I ended up doing after I got frustrated > with > > that error. I was following the book 'Head First SQL' which was > suggesting > > that you could do something like what this user was trying in this stack > > overflow thread: > > > > http://stackoverflow.com/questions/19175240/re-arranging-columns-in- > > mysql-using-position-keywords-such-as-first-second > > > > But the answer in that thread too suggests that this is wrong. So is the > Head > > First SQL book just referring to an outdated syntax that doesn't work > > anymore? I can't imagine that it never worked if it's in that book. But > hey ya > > never know! ;) > > Given the title of the book is "Head First SQL" and not "Head First MySQL" > it probably isn't exclusively using syntax for MySQL. While SQL is a > standard the various SQL databases are not completely identical with the > syntax they support. This may be due to not completely conforming to the > standard, using different versions of the SQL standard, or that there is > not standard for that operation. > > Best regards, > Jesper Krogh > MySQL Support > > > -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
