0

My database contains 'n' number of columns:

  name   Phone_no  Person_1    Person_2      Person_3     Person_4
  john     123        1           2            3            4
  Nolan   1234        23          34           1            5

  • If the Phone_no is 1234 then I want to delete the columns Person_1, Person_3.

  • I know about the column numbers(Column 3( Person_1), Column 5( Person_3 )) which has to be deleted.

  • Is there any way to delete the multiple columns through a single SQL statement.
Vikas Vijayan
  • 61
  • 8

2 Answers2

1

Is this what you want?

update mytable set person_1 = null, person_5 = null
where phone_no = 1234

I understand that by delete columns person_1 and person_3 where phone_no is 1234 you mean set values to null in columns person_1 and person_3 where phone_no is 1234.

If you want to actually remove the columns, then it's a different question. In SQLite, you need to recreate the table:

create table tmp_table(
    name varchar(50),         -- adapt the datatypes and lengths to your requirement
    phone_no int,
    person_2 varchar(50),
    person_4 varchar(50)
);
insert into tmp select name, phone, person_2, person_4 from mytable;
drop table mytable;

create table mytable(
    name varchar(50),
    phone_no int,
    person_2 varchar(50),
    person_4 varchar(50)
);
insert into mytable select name, phone, person_2, person_4 from tmp_table;
drop table tmp_table;
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I want to delete the columns. If I set it to NULL it will be displayed in the database. – Vikas Vijayan Dec 20 '19 at 13:16
  • @VikasVijayan: do you want to *drop* (remove) the column? If you do that, the column will not exist in the table anymore (regardless of the value of `phone_no`), is this what you want? – GMB Dec 20 '19 at 13:18
  • Yes. I want to drop the column. I don't need those columns in the table. – Vikas Vijayan Dec 20 '19 at 13:22
0

Is there any way to delete the multiple columns through a single SQL statement.

Another way is to run a single SQLite3 statement multiple times: https://stackoverflow.com/a/16162224/3426192

e.g: SQLite 2021-03-12 (3.35.0) now supports: DROP COLUMN

social
  • 329
  • 3
  • 8