Change column values based on anothe table

Question

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?

Might be a duplicate: https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server — Tyron78, Oct 09 '17 at 07:30

score 1 · Accepted Answer · answered Oct 09 '17 at 07:34

1

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

answered Oct 09 '17 at 07:34

gotqn

42,737
46
157
243

Thanks that's it. Actyually, I didn't have a Table 1 (it was just a query) that's why I didnt think about inner joining, but since I can just create the table, it works. – Piston Oct 09 '17 at 08:46

score 0 · Answer 2 · answered Oct 09 '17 at 07:50

0

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

answered Oct 09 '17 at 07:50

Paolo V.

53
8

Change column values based on anothe table

2 Answers2