cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Alter table command not working for me

TimothyClotwort
New Contributor

I am a novice with databricks. I am performing some independent learning. I am trying to add a column to an existing table. Here is my syntax:

%sql

ALTER TABLE car_parts ADD COLUMNS (engine_present boolean)

which returns the error:

SyntaxError: invalid syntax File "<command-3097619422049343>", line 4 ALTER TABLE car_parts ADD COLUMNS (engine_present boolean)

In general I am having issues executing raw sql commands from these execution windows (cells) in my databricks community edition tool. I have also tried: sqlContext.sql("ALTER TABLE car_parts ADD engine_present boolean")

, which returns the error: ParseException: no viable alternative at input 'ALTER TABLE car_parts ADD engine_present' (line 1, pos 31)

I am certain the table is present as:

sqlContext.sql("SELECT * FROM car_parts")

works fine. I would grateful for any insights. Thank you.

1 REPLY 1

Ryan_Chynoweth
Honored Contributor III

Is the table you are working with in the Delta format? The table commands (i.e. Alter) do not work for all storage formats.

For example if I run the following commands then I can alter a table. Note - there is no data in the table but the table exists and I can alter the table.

create table if not exists add_col_test  (
col_one integer, 
col_two integer
)
USING DELTA 
; 
 
describe add_col_test; 
 
alter table add_col_test ADD columns (col_three boolean);
 
describe add_col_test;