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
– Lin Ma, Commented Aug 27, 2015 at 6:57
There's already a helpful question: stackoverflow.com/questions/11321491/… — Barmar
– Barmar, Commented Aug 27, 2015 at 7:09

Stanislovas Kalašnikovas · Accepted Answer · 2015-08-27 06:57:10Z

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, 2015 at 6:57

Stanislovas Kalašnikovas

9,0735 gold badges36 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hearner · Accepted Answer · 2015-08-27 06:58:15Z

1

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)

answered Aug 27, 2015 at 6:58

Hearner

2,7053 gold badges19 silver badges34 bronze badges

Comments

jarlh · Accepted Answer · 2015-08-27 07:08:41Z

0

(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...

answered Aug 27, 2015 at 7:08

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

MySQL create view error

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related