joining on concatonation?

2008/2/22, blackwater dev <blackwaterdev@gmail.com>:

I have a query that is driving me nuts. In one table we have data that is
split between two columns and I'm trying to pull in all values from another
table where that column is represented by one piece of data. Also, all the
info in column2 is unique but not in col 1.

table1

col1_pfx
col2_number
col3

Table2
col1
col3

update table1 set col3=(select col3 from table2 where
table2.col1=(table1.col1_pfx || table1.col2_number));

I've tried using a join and all other methods and nothing seems to work.
Table2 has a primary key on col1 yet when I do explain posgres still seems
to do a sqential scan on that column.

Any thoughts?

UPDATE table1 SET col3 = table2.col3
FROM table2
WHERE table2.col1=(table1.col1_pfx || table1.col2_number);

Osvaldo