How to update one columns data using another tables data TSQL
database, sql, sql-server, sql-update, t-sql
Solution
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))
Problem
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 ?