Re: 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