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.

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

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?

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`

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

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.

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.