Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    6

    Unanswered: I used reserved words in field names– workaround?

    Oops! I'm a beginner who made a beginner's error – I have a table with that uses reserved words for field names (the field names are First and ALL).

    I am restructuring the database, and want to copy two joined tables to one flat table. I'm using the following SQL structure:

    INSERT INTO new_table_name (field1, field2, field3...)
    SELECT main.filed1, main.field2, ... subtable.field1, subtable.field2 ... FROM main INNER JOIN subtable ON main.primarykey = subtable.foreignkey;

    The problem is that when the query runs, I get an error message as in running it tries to break up the query at First and ALL. I've tried putting single and double quotes (before the comma) around the words, but other than coloring the text green (I'm using PHPMyAdmin to run the query), I'm still getting an error message.

    #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 '"First", Street1, Street2, City, State, Zip, Phone1, Phone2, Phone3, Email, Pass' at line 1
    Eventually I will be renaming the fields to FirstName and AllAbove to avoid the problem. However, since the database is live at the moment, I'm hoping to copy to a new table first, THEN make the changes to the underlying PHP code. Any suggestions?

    Thanks,

    Rhythm

  2. #2
    Join Date
    Oct 2006
    Posts
    6
    Did I post too soon?

    I just changed the name in the NEW table to FirstName and AllAbove, but left the original tables alone. I then made sure the order of the insert into fields and the select fields were the same, just named FirstName in the insert into section, and main.First in the select section. It seemed to work—or am I missing something?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for future reference, you'll want to post in the MySQL forum instead of the SQL forum, because the standard SQL answer is to use doublequotes to delimit column names, which will only work in MySQL if the ANSI_QUOTES option has been set

    meanwhile you can use the (non-standard) MySQL backticks to avoid the errors, e.g. `First`, `All`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2006
    Posts
    6
    My apologies for posting to the wrong forum (and for being nice about pointing to my mistake)—I'm sorry for cluttering things up.

    And thanks for the quick help—especially calling them "backticks." I'd seen them, but mistook them for single quotes, which, obviously didn't work. After a forehead slapping moment, it now makes sense.

    Again, thanks!

    Rhythm

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    If you are planning to work with other database systems as well, do yourself a favor, set MySQL to ANSI mode and stop using backticks.
    I would as well avoid the usage of double quotes for quoting columns names that are reserved words.
    Simply use names that need no quoting and you will save yourself a lot of trouble.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think the best thing to do is accept the pain right now, and design out any reserved word issues. Treat it as a lesson, we all do silly things, especailly when starting out, and sometimes later on ... not going to admit to mine (well ceretainly not here)

    just for your information here is a list of reserved words for MySQL V5.0

    Id agree with shammat and try to use ANSI standard SQL for as long as you can. Unless your employer is an xxxx SQL engine only house.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •