default value not showing up

[raman]

I have defined a field in my database table which has only two values -Yes or No, since the entries with Yes are few in number in the table I had entered only those and the rest I wanted that the table shoud take as 'No' by default.

 

So I have tried the following options for it for datatype ut in none of the cases the default value shows up.

 

alter table cryptovir change vacinfo vacinfo enum('No','Yes') NOT NULL;

 

alter table cryptovir change vacinfo vacinfo varchar(3) default 'No' NOT NULL;

 

Then I do select * from cryptovir where vacinfo='No';

But it says empty set

[Mchl]

You must update those entries, that have NULL value

 

UPDATE cryptovir SET vacinfo = 'No' WHERE vacinfo IS NULL;

 

As a side note: for Yes/No columns you should ideally use TINYINT type, and store 'Yes' as 1 and 'No' as 0

 

[raman]

When I give this updata command it says 0 rows affected.

[raman]

As far as I know tinyint data type is to store small numbers.

 

To use 0 and 1 I have to substitute 1 in place of 'Yes'.

[Mchl]

As far as I know tinyint data type is to store small numbers.

 

To use 0 and 1 I have to substitute 1 in place of 'Yes'.

 

tinyint is also comonly used to store boolean values, and a yes/no column is exactly that. Of course you can also use ENUM as in one of your examples. Just don't use varchar.

 

As for why UPDATE query affected 0 rows... I have no clue now... Could you paste a row from your table, where you would like to set 'vacinfo' to 'No' ? Doesn't have to be whole row.