1

Here's my query:

update slcm.m_user_master set Is_Active = '1' where M_USER_ID = '1'

while updating table it showing message data too long for this column . I have taken database IsActive database as bit.

this is table description
e Table

CREATE TABLE `m_user_master` (
  `M_USER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_Type_ID` int(11) DEFAULT NULL,
  `M_User_Name` varchar(50) DEFAULT NULL,
  `M_User_Name_Hindi` varchar(50) DEFAULT NULL,
  `User_Login_ID` varchar(30) DEFAULT NULL,
  `User_Password` varchar(30) DEFAULT NULL,
  `User_Mobile_No` int(12) DEFAULT NULL,
  `User_Email_ID` varchar(50) DEFAULT NULL,
  `Created_Date` datetime DEFAULT NULL,
  `Updated_By` varchar(50) DEFAULT NULL,
  `Updated_Date` datetime DEFAULT NULL,
  `Is_Active` char(1) DEFAULT NULL,
  `Active_From` datetime DEFAULT NULL,
  `Active_To` datetime DEFAULT NULL,
  `Created_By` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`M_USER_ID`),
  KEY `FK_M_User_Master_M_User_Type_Master` (`User_Type_ID`),
  CONSTRAINT `FK_M_User_Master_M_User_Type_Master` FOREIGN KEY (`User_Type_ID`) REFERENCES `m_user_type_master` (`User_Type_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8
Gynteniuxas
  • 7,035
  • 18
  • 38
  • 54
Nidhi
  • 59
  • 1
  • 8
  • 1
    Show the table description – Jens Aug 30 '16 at 08:02
  • M_USER_IDint(11) NOT NULL User_Type_IDint(11) NULL M_User_Namevarchar(50) NULL M_User_Name_Hindivarchar(50) NULL User_Login_IDvarchar(30) NULL User_Passwordvarchar(30) NULL User_Mobile_Noint(12) NULL User_Email_IDvarchar(50) NULL Created_Datedatetime NULL Updated_Byvarchar(50) NULL Updated_Datedatetime NULL Is_Activebit(1) NULL Active_Fromdatetime NULL Active_Todatetime NULL Created_Byvarchar(50) NULL – Nidhi Aug 30 '16 at 08:06
  • 1
    Please add it to your question using the edit function. Not as comment – Jens Aug 30 '16 at 08:07
  • 1
    for `bit` data-type the approach for adding a value is to prepend with `b` so in your case its `b'1'` check the doc here https://dev.mysql.com/doc/refman/5.7/en/bit-type.html – Abhik Chakraborty Aug 30 '16 at 08:08
  • Why do you need any more of the table description then what's been provided? – Ash Aug 30 '16 at 08:13

2 Answers2

3

Try the value for Is_Active without quotes like so:

update slcm.m_user_master set Is_Active = 1 where M_USER_ID = '1'

It's expecting a true/false (1/0) value which takes a single bit to store for Is_Active and it's getting a string instead which takes a byte or 8 bits to store. Hence the "too long" error.

Ash
  • 5,786
  • 5
  • 22
  • 42
0
update slcm.m_user_master set Is_Active = 1 where M_USER_ID = 1

and you can see this :

Return Bit Value as 1/0 and NOT True/False in SQL Server

Community
  • 1
  • 1
Xushoujue
  • 1
  • 2