Update fails error...pls help me out...
-
Hello friends..
Can any body guide me why this type of error getting me again and again
CREATE TABLE [emp] (
[id] [int] NOT NULL ,
[name] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO EMP(ID,NAME,CITY) VALUES(1,'JOHN','COSP')
INSERT INTO EMP(ID,NAME,CITY) VALUES(2,'MUTI','CKOP')
INSERT INTO EMP(ID,NAME,CITY) VALUES(3,'INVO','KOLE')
INSERT INTO EMP(ID,NAME,CITY) VALUES(4,'SHOL','LOPN')
INSERT INTO EMP(ID,NAME,CITY) VALUES(5,'PHIL','STAT')
CREATE TABLE [dept] (
[id] [int] NULL ,
[name] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO DEPT (ID,NAME) VALUES(55,'PHIL')
INSERT INTO DEPT (ID,NAME) VALUES(85,'SHOL')
INSERT INTO DEPT (ID,NAME) VALUES(65,'MUTI')
UPDATE EMP SET ID = (select isnull(dept.id,0) from dept where emp.name = dept.name )
Error : - Server: Msg 515, Level 16, State 2, Line 8
Cannot insert the value NULL into column 'id', table 'Employees.scott.emp'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Regards,
Papillon
-
That is because you are missing the where clause in the update statement. As a result where the names do not match between the two tables, the SQL will try to update it with a NULL value and fail.
Instead, you can do this:
UPDATE EMP
SET ID = ISNULL(DEPT.ID, 0)
FROM EMP, DEPT
WHERE EMP.NAME = DEPT.NAME
-
or with a join
UPDATE EMP
SET ID = ISNULL(DEPT.ID, 0)
FROM EMP LEFT JOIN DEPT ON EMP.NAME = DEPT.NAME
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply