Jump to content

Altering Existing Table With Data


JipThePeople

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/289540-altering-existing-table-with-data/
Share on other sites

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 ;

 

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. 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.