CODEWITHSUNDEEP
sqlsql-serverdatabase
Fation Hadri
Fation Hadri

Reputation: 150

SQL Server updating new column based on another column from a different table

This is my situation:

Hint: COLUMN (NUMBER_IDENTITY) on 2 tables have the same value but those 2 table have no relationship with each other (this is my choice for my situation)

Early database state

NOW

I have table CONTRACTS (ID, NUMBER_IDENTITY, USER_ID)

I add new column in SUB_CONTRACTS (ID, NUMBER_IDENTITY, USER_ID)

My problem

I should update USER_ID column in SUB_CONTRACTS with the same value that exists in CONTRACTS. I can do this update because NUMBER_IDENTITY column of the two tables is the same. So SUB_CONTRACT is complementary of CONTRACT.

Question

How to build a function, procedure or query in SQL Server that will solve my problem? Can I make a function that update in the same time all the column that meets this condition? I can solve in mini solution with query but I can't join all this mini query to solve my problem.

Upvotes: 2

Views: 1759

Answers (1)

Seibar
Seibar

Reputation: 70353

update SUB_CONTRACTS
set USER_ID = CONTRACTS.USER_ID
from SUB_CONTRACTS
inner join CONTRACTS on (SUB_CONTRACTS.NUMBER_IDENTITY = CONTRACTS.NUMBER_IDENTITY)

This syntax will work for SQL Server. It will update all USER_ID values in SUB_CONTRACTS to match the USER_ID values in CONTRACTS where the NUMBER_IDENTITY value is the same in the two tables.

Upvotes: 4

Related Questions