Altering Existing Table With Data

[JipThePeople]

I have a table with lots of records. My table has the following field, with records containing either NULL or '1' that I need to modify.

 

-- Current 

`archived` int(1) unsigned DEFAULT NULL

 

But I need to modify it so that it is NOT NULL and defaults to '0'. I have tried the following but it throws an error. 

 

-- Error "#1265 - Data truncated for column 'archived' at row 1"

ALTER TABLE `proposal_info` MODIFY `archived` INT( 1 ) UNSIGNED NOT NULL DEFAULT '0' 

 

-- SAME AS ABOVE EXCEPT NO QUOTES AROUND 0 - Error "#1265 - Data truncated for column 'archived' at row 1"

ALTER TABLE `proposal_info` MODIFY `archived` INT( 1 ) UNSIGNED NOT NULL DEFAULT 0

 

 

I need to modify the table so that the records that are NULL for that field, will be updated to '0'. I need the records that are currently '1', to retain that value.

[Barand]

Update the NULLs to 0 first.

If you only store 0 or 1 use tinyint (Single byte instead of 4)

UPDATE proposal_info
SET archived = 0
WHERE archived IS NULL;

ALTER TABLE `proposal_info` 
CHANGE COLUMN `archived` 
`archived` TINYINT NOT NULL DEFAULT 0 ;

[JipThePeople]

 

Update the NULLs to 0 first.

If you only store 0 or 1 use tinyint (Single byte instead of 4)

UPDATE proposal_info
SET archived = 0
WHERE archived IS NULL;

ALTER TABLE `proposal_info` 
CHANGE COLUMN `archived` 
`archived` TINYINT NOT NULL DEFAULT 0 ;

Thank you! That is a good solution.