CODEWITHSUNDEEP
mysqlsql
bsky
bsky

Reputation: 20222

Sql syntax error on DROP TABLE

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?

Upvotes: 2

Views: 7198

Answers (1)

user9152967
user9152967

Reputation:

  • 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`) 
);

Upvotes: 4

Related Questions