MySQL create view error

Question

Here is my statement for create view and related error message, using MySQL/MySQL WorkBench. I tried the select -- from -- where works. Any hints are appreciated. :)

CREATE VIEW [Products Above Average Price] AS            
SELECT ProductName,Price
FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products)

Error Code: 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 '[Products Above Average Price] AS #how to? SELECT ProductName,Price ' at line 1

Works for me, thanks @jarlh. If you could have a formal answer, I can help to mark as answer to benefit other people met with the same issue as well. :) — Lin Ma, Aug 27 '15 at 06:57
There's already a helpful question: http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Barmar, Aug 27 '15 at 07:09

score 1 · Accepted Answer · answered Aug 27 '15 at 06:57

1

You have to use ANSI standard double quotes "" instead of [] brackets.

CREATE VIEW "Products Above Average Price" AS 
SELECT ProductName,Price
FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products)

answered Aug 27 '15 at 06:57

Stanislovas Kalašnikovas

9,203
4
32
59

score 1 · Answer 2 · answered Aug 27 '15 at 06:58

Here is how view creation is supposed to look like in MySQL

Create View `MyViewName` as 
    Select
        col1, col2, col3
    From
        myTable T

In your case, the problem occures because of [] which aren't used in MySQL. Replace your query with

CREATE VIEW `Products Above Average Price` AS            
    SELECT ProductName,Price
    FROM Products
    WHERE Price>(SELECT AVG(Price) FROM Products)

score 0 · Answer 3 · answered Aug 27 '15 at 07:08

(Copied comment) ANSI SQL has double quotes ("") for delimited identifiers, e.g.

CREATE VIEW "Products Above Average Price" AS...

MS SQL Server also has [] as a vendor specific way to delimit identifiers, e.g.

CREATE VIEW [Products Above Average Price] AS...

MySQL has back-ticks, e.g.

CREATE VIEW `Products Above Average Price` AS...

MySQL create view error

3 Answers3