Jump to content

Altering Existing Table With Data


JipThePeople
Go to solution Solved by Barand,

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
Share on other sites

  • Solution

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 ;
  • Like 1
Link to comment
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 ;

Thank you! That is a good solution. 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.