Update record from another table based on condition

Transact-SQL
1

Hi Friends,

I have two table, one with basic employee details and another table contain the Address details ( Permanent and Communication Address).

I have to update the "EMPLOYEEBASIC" table with the "ADDRESSMASTER" table. The condition is if any of CommunicationAddress Column are blank the EmployeeBasicDetails need to be updated with PermanentAddress Column.

Please find the below script to understand it better

CREATE TABLE [dbo].[EmployeeBasic](
[EMPID] [bigint] NOT NULL,
[Name] varchar NOT NULL,
[Address1] varchar NULL,
[Address2] varchar NULL,
[City] varchar NULL,
[State] varchar NULL,
[Country] varchar NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[AddressMaster](
[AddressID] [bigint] NOT NULL,
[EmpID] [bigint] NOT NULL,
[PermanentAddress1] varchar NOT NULL,
[PermanentAddress2] varchar NOT NULL,
[PermanentCity1] varchar NOT NULL,
[PermanentState1] varchar NOT NULL,
[PermanentCounty1] varchar NOT NULL,
[CommunicationAddress1] varchar NULL,
[CommunicationAddress2] varchar NULL,
[CommunicationCity] varchar NULL,
[CommunicationState] varchar NULL,
[CommunicationCountry] varchar NULL
) ON [PRIMARY]

INSERT [dbo].[EmployeeBasic] ([EMPID], [Name], [Address1], [Address2], [City], [State], [Country]) VALUES (1001, N'Jude', NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[EmployeeBasic] ([EMPID], [Name], [Address1], [Address2], [City], [State], [Country]) VALUES (1002, N'Test1', NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[EmployeeBasic] ([EMPID], [Name], [Address1], [Address2], [City], [State], [Country]) VALUES (1003, N'Kesar', NULL, NULL, NULL, NULL, NULL)
GO

INSERT [dbo].[AddressMaster] ([AddressID], [EmpID], [PermanentAddress1], [PermanentAddress2], [PermanentCity1], [PermanentState1], [PermanentCounty1], [CommunicationAddress1], [CommunicationAddress2], [CommunicationCity], [CommunicationState], [CommunicationCountry]) VALUES (1, 1001, N'P_Addrs1', N'P_Addrs2', N'CityName', N'StatName', N'Country', NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[AddressMaster] ([AddressID], [EmpID], [PermanentAddress1], [PermanentAddress2], [PermanentCity1], [PermanentState1], [PermanentCounty1], [CommunicationAddress1], [CommunicationAddress2], [CommunicationCity], [CommunicationState], [CommunicationCountry]) VALUES (2, 1002, N'P_Addre1002', N'P_Address21002', N'City', N'State', N'Country', N'C_Addrs1002', N'C-Address22002', NULL, NULL, N'Country')
GO
INSERT [dbo].[AddressMaster] ([AddressID], [EmpID], [PermanentAddress1], [PermanentAddress2], [PermanentCity1], [PermanentState1], [PermanentCounty1], [CommunicationAddress1], [CommunicationAddress2], [CommunicationCity], [CommunicationState], [CommunicationCountry]) VALUES (3, 1003, N'P_Addre1003', N'P_Address21003', N'City', N'State', N'Country', N'C_Addrs1003', N'C-Address22003', N'City', N'State', N'Country')
GO

Goal is In the above "EmployeeBasic" table except EmployeeID 1003 the PermanentAddress column (PermanentAddress1, PermanentAddress2, PermanentCity1,PermanentState1, PermanentCountry1) need to be updated from "AddressMaster" table. Because the Communication Address column have NULL Values.

If CommunicationAddress column from "AddressMaster" have all the NOT NULL value then we can update the CommunicationAddress column Value to EmployeeBasicTable columns.

This is legacy one, so the table structure is designed like this :frowning:

Can you please help me to get this update statement

UPDATE E
SET E.[Address1] = A.CommunicationAddress1,
E.[Address2] = A.CommunicationAddress2,
E.[City] = A.CommunicationCity,
E.[State] = A.CommunicationState,
E.[Country] = A.CommunicationCountry
FROM [dbo].[EmployeeBasic] E
JOIN [dbo].[AddressMaster] A ON A.EmpID = E.EMPID

I think we have to add some NULL based condition with COLLACE but i am unable to complete.

All your help are much appreciated friends..

Please ask if you need any details.

2

Hi Friends,

Please let me know if you need any details.

3

I may have misunderstood, are you worried that there is no data in the master CommunicationAddress column?
If there is data in the other columns do you still want to update?

As written it seems the update will occur when ever EmpID matches.

4

EmpId should match, and if any communication columns value is null or empty then we have to take permanent address columns.

5

You might try something like:

UPDATE E
SET E.[Address1] = CASE WHEN A.CommunicationAddress1 IS NULL THEN A.PermanentAddress1 ELSE A.CommunicationAddress1 END, 
	E.[Address2] = CASE WHEN A.CommunicationAddress1 IS NULL THEN A.PermanentAddress2 ELSE A.CommunicationAddress2 END, 
...