SQL: Drop Column Syntax Error

Question

I want to drop a column from my table. I have tried executing the following syntax:

ALTER TABLE user
DROP COLUMN departmentid;

On execution, it hits me with an error stating that the statement could not be prepared. Can anyone tell me why it's doing this? I get the same error when I try renaming a column.

I am using MySQL - if it helps, I am on the SQL query builder provided by w3schools. — Sohail Arif
– Sohail Arif, Commented Oct 20, 2015 at 15:05

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

If you are using MySQL, Try using

DROP departmentid;
Use BackTicks 'column name' in the query.
It might not be possible to DELETE, INSERT or ALTER a table in online query builders. because once you drop tables, others cannot play around with same table. So I dont thing you have privileges to do so. If you are learning install xamp which has installed apache server and MySQL.

Hope this info helps!

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 20, 2015 at 15:09

Sahith Vibudhi

5,2452 gold badges36 silver badges37 bronze badges

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

5 Comments

Sohail Arif Over a year ago

Already tried that, gives me the same "cant prepare statement" error.

Sohail Arif Over a year ago

I am using the query builder provided by w3schools, it might be possible that they don't grant privileges to do so. However, some of the example statements they provide allow you to drop, alter and insert. I create a new table, try renaming or dropping a column, doesn't work!!

Sohail Arif Over a year ago

I have got wamp installed on my personal laptop, I am currently on a work machine, just doing some small sql revision, thought I go over what I already know, I think you're right about the privileges. I've already tried it with back ticks, that's the first thing I tried lol. Thanks for your help, much appreciated!

Sahith Vibudhi Over a year ago

Glad that Helped :) . If that answer was helpful you can tick it right, or upvote. Just telling :).

Sohail Arif Over a year ago

Thank you, I have to earn a reputation of 15 before I can upvote.. but sure!

Jasmine John · Accepted Answer · 2020-11-18 18:02:25Z

I solved the problem by executing below query: I need to remove a column and all the entries from that column to free my DB size my initial table structure is as shown below:

CREATE TABLE words(_id,word,synonyms,favorite,history,updDate)

And I wanted the table in below form

CREATE TABLE words(_id,word,favorite,history,updDate)

So I executed below query and it removed "synonyms" column

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(_id,word,favorite,history,updDate);
INSERT INTO t1_backup SELECT _id,word,favorite,history,updDate FROM words;
DROP TABLE words;
CREATE TABLE words(_id,word,favorite,history,updDate);
INSERT INTO words SELECT _id,word,favorite,history,updDate FROM t1_backup;
DROP TABLE t1_backup
COMMIT;

Collectives™ on Stack Overflow

SQL: Drop Column Syntax Error

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related