Append A Value To The Existing Value In SQL Server

This blog will help users update a record while keeping the existing record value if it exists.

Introduction

This blog will help users update a record while keeping the existing record value if it exists. My initial requirement was to just update the record but after some time, there was a case in my project to update the same record from two different pages.

So, in this case, I rneeded to append a query. Please check the below query to append a record.

I have given a summary of the below three approaches to append an existing record.

1. Check if their existing value is not present; then, do not append the record, just update it.
2. If the value exists, then append the new value with comma separation.
3. Update salary will do the sum of another salary with existing salary.

Create Table

I have created an ‘Employee’ table design which is given below.

CREATE TABLE[dbo].[Employee](
    [EMPLOYEE_ID][bigint] NOT NULL, [FIRST_NAME][varchar](50) NOT NULL, [LAST_NAME][varchar](50) NOT NULL, [SALARY][bigint] NOT NULL, [JOINING_DATE][datetime] NOT NULL, [DEPARTMENT][varchar](50) NOT NULL) ON[PRIMARY]

Insert Record into table

INSERT INTO Employee VALUES (1,'Sagar', 'Shinde', 25000, 2017-02-01,'Developer');
INSERT INTO Employee VALUES (2,'Swapnil', 'Shinde', 30000, 2016-02-01,'Doctor');
INSERT INTO Employee VALUES (3,'Pankaj', 'Shinde', 30000, 1000-02-01,'');

Requirements

Update the first name for employee ID = 1. Append the middle name initial in the first name.

Update Query

UPDATE Employee SET FIRST_NAME=FIRST_NAME+', '+'H' WHERE EMPLOYEE_ID=1

Result

SELECT * FROM Employee WHERE EMPLOYEE_ID=1

 

Update SALARY for employee ID =2. Add ₹ 300 in the existing salary.

Update Query

UPDATE Employee SET SALARY=CASE WHEN SALARY IS NULL OR SALARY='' THEN '300'
ELSE SALARY+'300' END WHERE EMPLOYEE_ID=2

Result

SELECT * FROM Employee WHERE EMPLOYEE_ID=2

 

Update DEPARTMENT for employee ID = 3. Currently, there is no existing value there. In the below query, we have checked if the DEPARTMENT value is NULL or blank.

Update Query

UPDATE Employee SET DEPARTMENT=CASE WHEN DEPARTMENT IS NULL OR DEPARTMENT='' THEN 'Agree' ELSE DEPARTMENT+', '+'Agree' END WHERE EMPLOYEE_ID=3

Result

SELECT * FROM Employee WHERE EMPLOYEE_ID=3

 

Thanks for reading this blog. I hope you understand the concept. Please comment or message me in case you have any questions.