Skip site navigation (1) Skip section navigation (2)

Re: joining on concatonation?

From: "Osvaldo Kussama" <osvaldo(dot)kussama(at)gmail(dot)com>
To: "blackwater dev" <blackwaterdev(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: joining on concatonation?
Date: 2008-02-23 15:30:33
Message-ID: 690707f60802230730k3d1d3d93g9c5473d112619d2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
2008/2/22, blackwater dev <blackwaterdev(at)gmail(dot)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

In response to

pgsql-general by date

Next:From: DeeDate: 2008-02-23 16:10:29
Subject: Invalid username specified: A required privlege is not held by the client
Previous:From: Jorge GodoyDate: 2008-02-23 13:47:43
Subject: Re: need some help on figuring out how to write a query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group