Wednesday, October 10, 2007

SQL INSERT statement with 'Reserved Word' Column Name

I wonder, who was the genius who created a table column, named 'AND'. In any software development, it is fundamental not to use reserved words besides their documented usage. Today my colleague asked me how to create SQL INSERT statement for a column consists of reserved word 'AND'. I have to make some trials and errors kinda research since I had never came across a table as ridiculous as this.

Although, it'll never work, I've to tried below INSERT statement anyway.
INSERT INTO mytable (id, and) VALUES (1, 'TEST');
By running above INSERT statement, you will received below error message.
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 'and) VALUES (1,'TEST')' at line 1
Some references I got from the Internet, they said it could be solved just by enclosing the column name with quotes ('and'). I've tried and it doesn't work.
Then I've came across MySQL Online Reference, where it stated there are three ways or syntax you could use to refer to a column, which are as below:
  1. <column_name>
  2. <table_name>.<column_name>
  3. <database_name>.<table_name>.<column_name>
So to solve my friend's problem, we can jez easily rephrase our insert statement as below:
INSERT INTO mytable (id, mytable.and) VALUES (1, 'TEST');
So by appending the table name prior to the column name (separated by dot '.'), we have turned this statement into a valid INSERT statement. Walla.

1 comment:

Unknown said...

beside that method you can do sth like this: [and] and will work too :)