Replacing int value with null-CodePudding

when I have an int column and most of the data is a number, but I have a few where there is no data and I am trying to fill it in with a 'null'. how do you go about this? (In summary a 'null' value in place of an int).

Thank you

CREATE TABLE EmployeeSalary (EmployeeID int, JobTitle varchar(50), YearsOfService int, Salary int );

INSERT INTO EmployeeSalary VALUES (101, 'Manager', 20, 80000), (102, 'Desk Clerk', 5, 30000), (103, 'Maintanence', 10, 60000), (104, 'Sales', 'null', 'null'), (105, 'Operations Manager', 'null', 'null') ;

CodePudding user response：

Remove quotes around null and put NULL in place of 'null'...

INSERT INTO EmployeeSalary (emp_id, emp_post, how_much_year, emp_salary) VALUES
       (1, 'Computer Engineer', 20, NULL),
       (2, 'Computer Scientist', NULL, 100000);

CodePudding user response：

The value 'NULL', with single quotes around it, is a string literal. You want to use NULL, without quotes:

INSERT INTO EmployeeSalary (EmployeeID, JobTitle, YearsOfService, Salary)
VALUES
    (101, 'Manager', 20, 80000),
    (102, 'Desk Clerk', 5, 30000),
    (103, 'Maintanence', 10, 60000),
    (104, 'Sales', NULL, NULL),
    (105, 'Operations Manager', NULL, NULL);

Note that I also added the target colums of the insert, which is considered best practice.

CodePudding user response：

You are trying to save text value in YearOfService column. Whenever you wrap any value in-between apostrophe i.e. ' then it becomes a text value .

Use null instead of 'null' it should work.

Valid insert query:

INSERT INTO EmployeeSalary VALUES
(101, 'Manager', 20, 80000),
(102, 'Desk Clerk', 5, 30000),
(103, 'Maintanence', 10, 60000),
(104, 'Sales', null, null),
(105, 'Operations Manager', null, null)
;

Please refer this db-fiddle