Simple SELECT statement fails with syntax to use near , ORA-00906 , syntax error at or near or syntax near the keyword
Okay, Table
is a reserved keyword in all variants of SQL.
If you want to call a table Table
, and use it in a statement, you have to tell your sql engine that it is an identifier. To do this you need to use Identifier Qualifiers.
for (MS SQL Server) TSQL use square brackets
SELECT * FROM [Table];
for MySQL use `
SELECT * FROM `Table`;
for Oracle and PostgreSQL use quotation marks,
these are standards compliant.
SELECT * FROM "Table";
for SQLite you can use any of the above, but quotation marks are prefered.
The Identifier Qualifiers tell the engine that this is an identifier (the name of an object.) Not the name of a keyword, even if they happen to be the same. Without your guidance the query engine can get confused and report an error, or worse, do something unexpected.
Using Identifier Qualifiers is good practice, even if the identifers are not keywords.
They better define statements for all parsers, including the fleshy kind.
Naming objects after keywords is generally considered bad practice. So you should try to avoid making identifers the same as keywords. The occasions when a reserved keyword is descriptive of the contents of a table are rare, see the footnote.
e.g. your table is not a Table
of tables.
The problem and advice is not limited to Tables
, Identifiers are required for all database objects inluding Schema
, Views
and the many types that exist, standard and vendor-specific.
Another form of good practice is to prefix Table
indentifiers with a Schema
identifier, this helps the query engine a little.
When including the Schema
identifier, the identifer should be qualified,
for (MS SQL Server) TSQL use square brackets
SELECT * FROM [dbo].[Table];
for MySQL use `
SELECT * FROM `dbo`.`Table`;
for Oracle, PostgreSQL and SQLite use quotation marks
SELECT * FROM "dbo"."Table";
even if your Schema
is not named after a keyword, as should be the case.
For your reference, to help you avoid conflicts.
A list of TSQL Reserverd Keywords.
A list of MySQl Reserved Keywords.
A list of Oracle Reserved Keywords.
A list of SQLite Reserved Keywords.
A list of PostgreSQL Reserved Keywords.
Notable "gotcha's" include USER
and ERROR
, which seem to come up when designing systems.
Footnote:
There are occasions when using reseved words for object names may be semantically correct.
Consider the contrived example of an information system for a furniture shop. In this scenario, a table of tables (kitchen, garden, dining, apothecary etc.) may be correct. So, you could argue Table
was the correct identifier.
If you always use Identifier Qualifiers, you won't get burned.
Syntax error near , with postgres go lib pq
You should use ($n
) instead of ?
for the bound parameters.
SQL error: Incorrect syntax near the keyword 'User'
User
is a reserved keyword, so you must use square brackets to make it explicit that you mean the object named "User" it, i.e. use [User]
instead of User
.
C# Syntax Error Near Table Name
User
is a reserved keyword on T-SQL. You should use it with square brackets like [User]
Also using parameterized queries always a good practice.
And Never store passwords in plain text! Use SHA-512 hash.
Syntax error for an SQL query in a WCF service
You don't have to quote the table name - in your example the table name is specified as TestTable
in the second example but it comes out to 'TestTable'
in the first example. Table names shouldn't be surrounded by quotes.
If you're using a reserved keyword as a table name, then you should surround the table name with backticks, for example:
`TestTable`
Note that it's not the single quote character.
See the documentation - 9.2 Schema object names
Getting an inteccorect Syntax error on sp_UpdateStockItemAmount
you have two SQL instructions in one line and sp_UpdateStockItemAmount
is not recognized as stored procedure invocation
add exec
:
"DELETE FROM DBO.OrderItems WHERE stockItemId=@stockItemId; exec sp_UpdateStockItemAmount @Id, 1"
Add user method not working: Incorrect syntax near the keyword 'User'
User is a Keyword in SQL. Put it into Brackets [User]
Related Topics
How to Find the Number of Occurrences of a Particular Character in a String Using SQL
How to Create a "Unique" Constraint on a Boolean MySQL Column
Count Null Values from Multiple Columns with SQL
Return Rows from Insert with on Conflict Without Needing to Update
Select Values That Begin with a Number
Delete All Records Except the Most Recent One
How to Add 10 Seconds in Current_Timestamp SQL ( Oracle )
Sql: Many-To-Many Table and Query
Is Order Guaranteed When Inserting Multiple Rows with Identity
Entity Framework Hitting 2100 Parameter Limit
How to Transform Rows to Columns
How to Label "Transitive Groups" with SQL
Read the Log File (*.Ldf) in SQL Server 2008
Difference Between Varchar(500) VS Varchar(Max) in SQL Server
Insert Xml File in SQL via Ssis
Recover Unsaved SQL Query Scripts
Libraries for Ado.Net to Rapidly Bulk Insert Data into a Database from a .CSV File