How do I lookup a value in a table and insert lookup result in the same table?

I created a table from a CSV file. Although the table has all the data we could ever need for this project, the resulting table is not normalized. My task is to clean and normalize this table.

1. Given a field value Value1 from TableA, how do I lookup that value in TableB and place the PK from TableB in a FK column in TableA?

2. Same as Question 1 exception I need to lookup multiple columns. Given field values Value1 and Value2 from TableA, how do I lookup that combination in TableB and pla开发者_如何学Cce the PK From TableB in a FK column in TableA?

This should work for #1:

UPDATE TableA
SET FKColumn = b.PKColumn
FROM TableA as a
INNER JOIN TableB as b
ON a.Value1 = b.Value1

For #2:

UPDATE TableA
SET FKColumn = b.PKColumn
FROM TableA as a
INNER JOIN TableB as b
ON a.Value1 = b.Value1 AND a.Value2 = b.Value2

UPDATE TableA SET FKColumn = TableB.PKColumn
FROM TableB
WHERE TableA.Value1 = TableB.Value1

UPDATE TableA SET FKColumn = TableB.PKColumn
FROM TableB
WHERE TableA.Value1 = TableB.Value1 AND TableA.Value2 = TableB.Value2

UPDATE TableA a 
SET a.fk_col = (select b.pk_col 
                from TableB b 
                where b.value = a.value);

And

UPDATE TableA a 
SET a.fk_col = (select b.pk_col 
                from TableB b 
                where b.value = a.value1 
                and b.value = a.value2);

Logically, it should be something like this:

UPDATE TableA
   SET FK_col = 
       (SELECT PK_col
          FROM TableB
         WHERE TableB.TableA_field = TableA.field)
 WHERE <your filter criteria on TableA>

However, at least in MySQL, you can't directly reference TableA in sub-queries like this in some circumstances, but this can be worked around in different ways depending upon the circumstances. I'm not sure if the same applies to SQL Server or not - never touched it.

Edit: I think the other answers posted in the meantime are probably better than mine! ;)