Sql syntax error on DROP TABLE

Question

I am trying to create a MySQL table with the following code:

CREATE DATABASE 
  IF NOT EXISTS myusers;USE 
    DROP TABLE 
    DROP TABLE IF EXISTS `myusers`.`users`;CREATE TABLE `myusers`.`users` 
                 ( 
                              `username`  VARCHAR(45) NOT NULL, 
                              `password`  VARCHAR(45) NULL, 
                              `firstname` VARCHAR(45) NOT NULL, 
                              `lastname`  VARCHAR(45) NULL,
                              `phone`     INT NULL, 
                              PRIMARY KEY (`username`) 
                 )

However, I am getting this error:

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 'DROP TABLE DROP TABLE IF EXISTS myusers.users' at line 2

I have limited knowledge of MySQL. From what I know about SQL syntax this looks fine.

Any idea what could be the issue here?

Seems like you have "DROP TABLE" twice. I don't know the mysql syntax, but this seems likely to be incorrect. — Dave Costa
– Dave Costa, Commented Dec 29, 2017 at 19:09
Where to begin? CREATE DATABASE <name needed here>. Then you have IF NOT EXISTS myusers;USE which seems to make no sense. etc, etc. The only advice I can give is to read the manual and actually look up the syntax for each individual command you're trying to write.... — MatBailie
– MatBailie, Commented Dec 29, 2017 at 19:10

score 4 · Accepted Answer · 2017-12-29 19:13:41Z

4

USE should be followed by a database name.
There is extra DROP Table.
Drop the table if exists DROP TABLE IF EXISTS users first.
Then create the table.

Like this:

CREATE DATABASE IF NOT EXISTS myusers; 
USE myusers;
DROP TABLE IF EXISTS `users`;

CREATE TABLE `myusers`.`users` 
    ( 
    `username`  VARCHAR(45) NOT NULL, 
    `password`  VARCHAR(45) NULL, 
    `firstname` VARCHAR(45) NOT NULL, 
    `lastname`  VARCHAR(45) NULL,
    `phone`     INT NULL, 
    PRIMARY KEY (`username`) 
);

edited Dec 29, 2017 at 19:13

answered Dec 29, 2017 at 19:10

user9152967

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

1 Comment

user9152967 Over a year ago

@MatBailie The first line is to create the database if not exists, I fixed it. But, still does makes little sense.

Collectives™ on Stack Overflow

Sql syntax error on DROP TABLE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related