CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Piston
Piston

Reputation: 95

Change column values based on anothe table

I have 2 tables. One with the "real" data, and the second one just have 2 columns with related values. For example:

TABLE 1:

ORG     NUM     INVOICE 
111    AE539     8532
222    AE232     129
333    BF952     1576
111    BF332     395
333    AE416     2685

TABLE 2:

 NUM     REAL_N
AE539     00705
AE232     00703
BF952     00701 
BF332     00712
AE416     00729

What I need is to replace, in Table 1, the NUM value with its corresponding REAL_N from Table 2. I though of doing a CASE but since Table 2 contains more than 1500 rows and will be updated every day with new rows, its not valid for me.

I need something that, for every Table 1 NUM value, checks table 2 to search that new value. So the result for this example would be:

TABLE 1:

ORG     NUM     INVOICE 
111    00705     8532
222    00703     129
333    00701     1576
111    00712     395
333    00729     2685

How can I do that?

Upvotes: 1

Views: 49

Answers (2)

Paolo V.
Paolo V.

Reputation: 53

This would actually do what you need. 

UPDATE A SET A.NUM = B.REAL_N
FROM [TABLE 1] A INNER JOIN [TABLE 2] B
ON A.NUM = B.NUM

Upvotes: 0

gotqn
gotqn

Reputation: 43636

You need to perform INNER JOIN to match the rows and then perform an UPDATE:

UPDATE [TABLE 1]
SET [NUM] = B.[REAL_N]
FROM [TABLE 1] A
INNER JOIN [TABLE 2] B
    ON A.[NUM] = B.[NUM];

Upvotes: 1

Related Questions