CODEWITHSUNDEEP
sqlpostgresql
dmc7z
dmc7z

Reputation: 1717

Update a record based on a related column from another table

I have a table with a column - say, article_id - that corresponds to the primary key of another table. See below.

I want to populate the column 'lev' with the Levenshtein Distance, calculated as such: 

LEVENSHTEIN(table1.string, table2.title)

...where the article_id of table1 corresponds to the primary key of table2.

I'm using Postgres and this function is part of the fuzzystrmatch module.


The relevant part of the tables look like this:

table1
id    article_id   string   lev
1     134          'ace'
2     227          'bdg'
3     425          'hkl'

table2
id    title
134   'Some title abc'
227   'Some title def'
425   'Some title ghi'

How to achieve this?

Upvotes: 1

Views: 261

Answers (2)

sgeddes
sgeddes

Reputation: 62861

Not knowing anything about the library module you referenced, assuming the function exists, this should work:

UPDATE table1 AS t1 
SET lev = LEVENSHTEIN(t1.string,t2.title)
FROM table2 as t2
WHERE t1.articleid = t2.id

Upvotes: 2

unutbu
unutbu

Reputation: 880937

UPDATE table1 as t1 SET lev=LEVENSHTEIN(t1.string, t2.title)
FROM table2 as t2
WHERE t1.article_id = t2.id

Useful reference:

Upvotes: 2

Related Questions