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 ?
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))
Update TableIWantToCorrect
SET ForeignKey = s.id
FROM TableIWantToCorrect , TableWithIDs as s
WHERE UPPER(s.CompareName) = UPPER( (SUBSTRING(TableIWantToCorrect.Name, 4, 3))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With