CODEWITHSUNDEEP
sqlpostgresql
Peter Penzov
Peter Penzov

Reputation: 1668

Delete table column content

I have the following table:

car score description
Opel 30 43
Volvo 500 434
Kia 50 3
Toyota 4 4
Mazda 5000 4

How I can delete all the content of table column score without changing the table structure?

Expected result:

car score description
Opel 43
Volvo 434
Kia 3
Toyota 4
Mazda 4

Upvotes: 1

Views: 74

Answers (2)

Ian Ho
Ian Ho

Reputation: 21

I think the answer by gowner is ok.

However in case you have no permission to alter table structure, you cannot delete column.

And given the score field is not nullable, you cannot update the field to null.

You must be careful that updating the score to 0 may not be ideal. 0 may have different meaning in your table. Maybe minimum score is 1 and 0 is not a possible value in the field. Or a consensus in your organization that -1 means "no value". They should be relfected in the default constraint or guidelines of your organization.

I would prefer to be safe

UPDATE cars SET score = DEFAULT;

Upvotes: 1

gowner
gowner

Reputation: 337

As pointed out by Bergi, you have the option of setting all values in the column to NULL or 0, depending on what you need, or you can delete the entire column.

Solution 1:

UPDATE cars SET score = NULL;

or

UPDATE cars SET score = 0;

This will preserve the score column but set all the values to NULL or 0 respectively. Note that NULL and 0 are different things. NULL means the field is empty but 0 means the field has the numerical value 0.

If you don't need the score column anymore, you can delete it like this:

ALTER TABLE cars
DROP COLUMN score;

This will delete the column score and you will not be able to use it anymore.

Upvotes: 3

Related Questions