0

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.

InvisibleKid56
  • 33
  • 4
  • 1
    Do **not** repeat the target table of an UPDATE in the FROM clause. –  Feb 28 '22 at 13:49
  • @pringi: The link to a *MySQL* solution is misleading. Postgres has different (IMO better) syntax rules for `UPDATE`. – Erwin Brandstetter Mar 14 '22 at 15:41
  • Yes, I got my answer, thank you! Leaving out the target table in the FROM clause did the trick. Also the hint that it is weird that the reverse should result in a match was helpful. In the end, I went with this: update citation set source_id = s.id from sources s where right(s.signature, 7) = Reverse (substring (Reverse(citation.link), 22, 7)); – InvisibleKid56 Mar 15 '22 at 21:03

1 Answers1

0

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • That's actually an illegal form of joined update and you will not find such an example anywhere in the MS documentation. It'll also drive you nuts someday when a certain unpredictable set of circumstances occur and it pegs a bunch of CPUs and a 2 second query suddenly take more than 2 hours to execute. The correct for is to use the target table in the FROM clause with an alias and the UPDATE would target the alias. – Jeff Moden Mar 01 '22 at 00:01
  • @JeffModen: Remarkable approach to recommend the "MS documentation" for a Postgres solution. No "illegal form" here, and your "correct form" makes no sense. Seems like you are barking up the wrong tree. – Erwin Brandstetter Mar 01 '22 at 00:28
  • Heh. Gimme a minute to wash the egg off my face to thank you for the correction. I totally missed that. – Jeff Moden Mar 01 '22 at 00:32