CODEWITHSUNDEEP
mysqlsql
szxk
szxk

Reputation: 1839

Debugging MySQL CREATE TABLE from existing table statement?

Why is this giving me an error ?

I haven't found any good example code online that combines CREATE TABLE with a SELECT statement with WHERE.

CREATE TABLE tmp_year (source CHAR(3),
                       target CHAR(3),
                       val FLOAT,
                       PRIMARY KEY (source, target))
(SELECT source,
        target,
        val
 WHERE date>='2001-01-01'
 AND date<='2001-12-12')
FROM db;

error message:

ERROR 1064 (42000): 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 'WHERE date>='2001-01-01' AND date<='2001-12-12') FROM db' at line 1

Upvotes: 0

Views: 50

Answers (2)

Hamidreza
Hamidreza

Reputation: 3128

Try this my friend:

CREATE TABLE tmp_year AS
SELECT * FROM YOURTABLE
WHERE date>='2001-01-01'
AND date<='2001-12-12';

ALTER TABLE tmp_year ADD PRIMARY KEY(source, target);

Here is example in SQL Fiddle

Upvotes: 2

Jonathan Wommack
Jonathan Wommack

Reputation: 134

SQL expects FROM to immediately follow a SELECT clause's values and be before conditionals like WHERE.

Upvotes: 1

Related Questions