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

Last Reply 2 months ago By vijay9471

Posted 3 months ago

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.

You are viewing reply posted by: vijay9471 2 months ago.
Posted 2 months ago

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]
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