Update one column of table with data from another table in SQL Server

vijay947...

SOLVED CLOSED ACTIVE ANSWERED
User: vijay9471
Posted: on Oct 04, 2018 12:15 AM
Forum: SQL Server
Answer: 1
Views: 2631

Hello Everyone,

I have situation here there are 2 sql tables A and B earlier there is no relationship between them i have referenced table b id in table a.

Going forward i need to reference the table b id in to table a with some matching table a columns with table b columns and updating table b id in table a.

For example: table a has id(identity column), firstname, lastname, country code, company code, source company, hr cost center, work location

table 2 also has same columns as table b but problem is table 2 columns has null values for few rows.

so my business suggested me to match 4 columns in table a and table b for picking table b id and updating in table a

if 4 columns does not match then check 3 columns then 2 coloumns then 1 columns matching (similar like factorial way)

then if none of the columns matches then go for default value.

so how to write a sql query to loop through different conditions(almost 16 conditions) and updating id in table a from table b.

please anyone help me on this issue , it is very important for me. thanks in advance.

Download FREE API for Word, Excel and PDF in ASP.Net

vijay947...

0 10

Reply
ANSWER
User: vijay9471
Replied: on Oct 20, 2018 02:16 PM
Report

This is my below code i have used in my requirement. i have implemented different criteria by repeating conditions with order by triples doubles and single orders. (by begin and end) it performs mass update at a time.

ALTER procedure [dbo].[sp_populatingnewSubunitId]
as
begin
update t
    set [SubUnitID] = s.id 
                    FROM [dbo].[NewTravelerView] t
                    join [dbo].[tbl_SubUnit] s
                    on [CountryCode]= [WorkCountry]
                    and t.[SourceCompany]=s.[SourceCompany] and t.[HRCostCenter] =s.[HRCostCenter] and t.[WorkLocation]=s.[Location] and t.[WorkCompany]=s.[CompanyCode]
                    WHERE  t.SubUnitID is null
                    AND t.[WorkCountry] is not null and t.[SourceCompany] is not null and t.[HRCostcenter] is not null and t.[WorkLocation] is not null and t.[WorkCompany] is not null and s.[Flag]=1
end
end
GO

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Update one column of table with data from another table in SQL Server

Company

Explore

Follow us

Contact Us