0
IF COL_LENGTH('Characters', 'name') IS NULL
BEGIN
    ALTER TABLE `Characters` ADD `name` INT(32) UNSIGNED NOT NULL;
END

I am trying to write some sql that will insert a column name into the table "Characters" if it is not already existant, however I am getting errors which I do not understand (quite new to SQL) and I could use some help understanding why it is not working. I have gotten the IF part from another Question, and the ALTER part from the DBMS I'm using, PhpMyAdmin 2.11.4.

It is using MySQL 5.6 apparently and this is the error:

MySQL said: Documentation
#1064 - 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 'IF COL_LENGTH('Characters',     'name') IS NULL
BEGIN
    ALTER TABLE `Characters` AD' at line 1
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
AndrewB
  • 764
  • 1
  • 6
  • 25
  • Modifying DDL with SPs seems frought with difficulty; what are you trying to achieve? – fenway Oct 27 '13 at 17:29
  • What do you mean by "what are you trying to achieve"? I want to insert a new column to my table if the column doesn't already exist in that table. Or did you mean something else? – AndrewB Oct 27 '13 at 17:31
  • 1
    Please, have look at this: http://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist – Doro Oct 27 '13 at 17:50
  • I already looked at that one, it was all over my head and didn't seem to help my situation a whole lot by its complexity. – AndrewB Oct 27 '13 at 18:11
  • 1
    Schema should, in general, be static; the data within is dynamic. To have a need to manipulate your schema dynamically is suggestive of very poor design. Hence @fenway's question of *why* you're trying to manipulate your schema dynamically. See [What is the XY problem?](http://meta.stackexchange.com/a/66378) – eggyal Oct 27 '13 at 18:25
  • It is not bad design, the data I am working with will be changing regularly, however it can be predicted to some degree, by adding and deleting columns dynamically I can work with the data appropriately. – AndrewB Oct 27 '13 at 18:36
  • One table will have a whole bunch of rows, each of these rows have a name field. Now in another Table, each of those rows' "name" field needs to be a column name. – AndrewB Oct 27 '13 at 18:38
  • 1
    It's always possible to pivot your schema about an axis so that "columns" become records with common attributes, which would avoid any schema alterations at all. Without more specific context of the precise business problem that you are trying to solve with this design, I can only suggest that you reconsider. – eggyal Oct 27 '13 at 22:18
  • I've thought about a different way, it might work, if it doesn't I'll come back and explain it. Thanks for the suggestions. – AndrewB Oct 27 '13 at 23:11

0 Answers0