Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Select problem
February 1, 2006, 7:00 am
rate this thread
The following works in Access and MS SQL but not MySQL:
SELECT * FROM [CFG] WHERE [Id] = "XXX"
SELECT * FROM [USER] WHERE [Name] = "XXX"
SELECT * FROM [PRODUCT] WHERE  = "XXX"
In the last SELECT, the fields are numbered 1 to 10 in the PRODUCT table.
The reason I enclosed all by table names in [ and ] is because USER is a
special name in MS SQL.
The following works in MySQL:
SELECT * FROM CFG WHERE Id = "XXX"
SELECT * FROM 'USER' WHERE Name = "XXX"
But I cannot work around the last one where the field number is a number.
It is possible to configure MySQL so that it can recognise the [ and ]
How can I include in the WHERE clause where the field name is a number?
Any help greatly appreciated.
Re: Select problem
You can't use , you should use a pair of `` instead. Here's an example:
mysql> create table number(`user` varchar(255), `1` text );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into number values( 'test', 'hello' );
Query OK, 1 row affected (0.01 sec)
mysql> select * from number where `1` = 'hello';
| user | 1 |
| test | hello |
1 row in set (0.00 sec)
Re: Select problem
You can use any name you like if you enclose the field names in delimiters.
MySQL uses backticks (``) by default, and recent versions of MySQL can be
configured to use double-quotes ("") in compliance with the SQL standard.
Apparently MS Access uses square brackets for the purpose of delimiting
special identifers, though this is definitely nonstandard syntax and is not
likely to be compatible with other RDBMS implementations.
May I suggest that it is unusual -- and confusing -- to use an integer as an
identifier? I can't think of any programming language (C, Java, etc.) in
which this is permitted.
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum