How to update one columns data using another tables data TSQL

Question

I have just realised I have been capturing the wrong data for one column in my table. I have fixed the problem, however, the data I have captured thus far remains incorrect.

Let's name my tables TableIWantToCorrect and TableWithIDs

In TableIWantToCorrect, I have a foreign key to TableWithIDs. This is what is incorrect.

I am able to correct the data by comparing substring of a column in TableIWantToCorrect with a column in TableWithIDs.

So currently, I have

TableIWantToCorrect

Name            ForeignKey
123-abc-123        15
456-def-456        15
789-ghi-789        15

TableWithIDs

CompareName    id
abc            1
def            2
ghi            3

So I want to update TableIWantToCorrect to have the correct ForeignKey value when the substring in the Name equals the substring in Compare name. The position of the substring is always the same so I can use the Substring method.

My attempt :

Update TableIWantToCorrect
SET ForeignKey =
       (SELECT id 
        FROM TableWithIDs 
        WHERE UPPER(CompareName) = UPPER((SUBSTRING(TableIWantToCorrect.Name, 4, 3)))

The result :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

I know I have done something silly. What have I done incorrectly here ?

Solution 1

The error is because your subquery is returning more than one record for the UPDATE. To fix this, you can do this using a JOIN with your UPDATE

UPDATE t1
SET ForeignKey = t2.id
FROM TableIWantToCorrect t1
INNER JOIN TableWithIDs t2
    ON UPPER(t2.CompareName) = UPPER(SUBSTRING(t1.Name, 4, 3))

Solution 2

 Update TableIWantToCorrect
 SET ForeignKey =  s.id
 FROM TableIWantToCorrect , TableWithIDs as s
 WHERE UPPER(s.CompareName) = UPPER( (SUBSTRING(TableIWantToCorrect.Name, 4, 3))