SQL update query with substring WHERE clause

Question

I have a table called citation and a table called sources. I want to create a foreign key called "source_id" in the citation table. Citation has a text field "link" where the url contains an ID at least 7 digits long, 22 characters from the end of the string. Sources has a big integer field "signature" that contains just that ID at least 7 digits long. I want to use this ID to create a foreign key for as many as possible. The IDs 8 digits long I will fix afterward, probably manually, as there should not be many.

When I use this select query, it returns 10 values only (I had set up 10 entries to match so I could test this), with the correct ID matching.

select s.signature, c.link, s.id, c.id
from citation c, sources s
where right(s.signature, 7) = substring (Reverse(c.link), 22, 7);

However, when I use this update query with the same where clause, it updates all records of "sources_id" in the citation table.

update citation
set source_id = s.id 
from citation c, sources s
where right(s.signature, 7) = substring (Reverse(link), 22, 7);

What am I missing? It should only update the 10 records with matching IDs within the WHERE clause substrings.

Answer 1

The manual about UPDATE:

Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

So:

UPDATE citation c
SET    source_id = s.id
FROM   sources s
WHERE  right(s.signature, 7) = substring(reverse(c.link), 22, 7);

It's odd that the substring matches as reversed string, and what you mention about 8 digits, but all of that seems irrelevant to the question.