Inserting values to table when column value is not exact match-CodePudding

I have two SQL tables with schemas like

date, league_id, team_id, value_to_be_added
19-02-2002, 256, 120, null
19-02-2002, 256, 7, null
...
25-08-2005, 3, 3, null

and

team_id, team_name, league_id
120, some_team, 256
7, other_team, 120
...
3, third_team, 3

And would like to insert values to the first table from a third table that is like

date, team_name, league_id, value_to_be_added
19-02-2002, some_tAem, 256, 8
19-02-2002, oter_team, 256, 19

But I'm having problems as the team names in the last table are not exact matches to the table where I'm supposed to insert the values. The mistakes are not due to incorrectly typed but spelled differently

I'm using Postgres as my SQL. Could anyone hint at a query that could accomplish this?

CodePudding user response：

You can match the wrong team names with the correct ones based on similarity. Note that this requires a cross join between tables 2 and 3, which can be very costly if your tables are big.

with u as
(select team_name as wrong_name, value_to_be_added from Table3),
v as
(select team_name as correct_name, team_id from Table2),
w as
(select *, similarity(wrong_name, correct_name) as s
from u cross join v),
x as
(select distinct on(wrong_name) 
team_id, wrong_name, value_to_be_added, correct_name from w
order by wrong_name, s desc)
update Table1
set value_to_be_added = x.value_to_be_added
from x
where x.team_id = Table1.team_id

Fiddle