SQL Query to update department

Question

I have a special scenario to update my department without having any conflicts with the existing records.

Can someone tell me how to write a query for it?

Here is my need.

Table Dept:

DeptID             DeptName
-----------------------------    
D001               Accounts
D002               HR
D003               Dev
D004               Support

Now, I want to update one of the existing records and make sure that it should not allow duplicates (Either deptid or deptname and both).

Ex:

• Case 1: When I try to update D001, I should not give DeptID like D002, D003 or D004 as they are already existing.

• Case 2: When I try to edit DeptName, it should not accept any of the existing DeptNames

Answer 1

You'd be better off storing your department identifier as a INT / primary key that auto increments. That would take care of the ID's being unique.

ALTER TABLE Dept 
ADD DeptID int NOT NULL IDENTITY (1,1) PRIMARY KEY

If you really want the format D001, on your values, you can add this when you retrieve data:

SELECT  DeptID,
        'D' + RIGHT('000' + CAST(DeptID AS VARCHAR(3)), 3)
FROM    Dept

Then you only need to worry about department names being unique.

With the department names, firstly you should have application logic that prevents duplicate values being added and secondly, you can perform a check when you attempt to add a new value:

DECLARE @DeptToAdd VARCHAR(20) = 'Cleaning'

IF NOT EXISTS (SELECT 1 FROM Dept WHERE DeptName = @DeptToAdd)
BEGIN
-- INSERT CODE IF IT DOES NOT EXIST
  INSERT INTO Dept (DeptName)
  VALUES (@DeptToAdd)
-- or UPDATE
  UPDATE Dept
  SET DeptName = @DeptToAdd
  WHERE DeptId = 123
END

This will only insert or update the value if it doesn't already exist.

On top of this you can enforce this with a unique constraint.

Answer 2

AS @mxix points out, a UNIQUE CONSTRAINT will prevent you adding duplicates (and you should add them), but it will give you an error message if you try. Here's a query that does nothing if you try to add an existing value

INSERT INTO Dept(DeptID)
SELECT 'ANewValue' 
WHERE 'ANewValue' NOT IN (SELECT DeptId FROM Dept)

Answer 3

Check for the existance of deptid or deptname and then insert the values to the dept table,

For INSERT:

IF NOT EXISTS(SELECT DeptID 
              FROM Dept 
              WHERE DeptID = 'D005' OR DeptName = 'maintanance')
BEGIN
    INSERT INTO Dept (DeptID, DeptName) VALUES ('D005', 'maintanance')
END

For UPDATE:

Case1:

UPDATE Dept 
SET DeptID = 'D005' 
WHERE DeptName = 'some name'  AND DeptID NOT IN (SELECT DeptID FROM Dept)

Case 2:

UPDATE Dept 
SET DeptName = 'Some name'
WHERE DeptID = 'some id'  AND DeptName NOT IN (SELECT DeptName FROM Dept)