Thread: joining on concatonation?

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?

Thanks!

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