JipThePeople Posted July 7, 2014 Share Posted July 7, 2014 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 More sharing options...
Barand Posted July 7, 2014 Share Posted July 7, 2014 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 ; Link to comment https://forums.phpfreaks.com/topic/289540-altering-existing-table-with-data/#findComment-1484187 Share on other sites More sharing options...
JipThePeople Posted July 7, 2014 Author Share Posted July 7, 2014 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. Link to comment https://forums.phpfreaks.com/topic/289540-altering-existing-table-with-data/#findComment-1484197 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.