From: | Lew <noone(at)lewscanon(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Query question |
Date: | 2012-01-27 19:04:25 |
Message-ID: | jfusfr$uu8$1@news.albasani.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 01/26/2012 04:00 AM, John Tuliao wrote:
> I seem to have a problem with a specific query:
>
> The inside query seems to work on it's own:
>
> select prefix
> from john_prefix
> where strpos(jpt_test.number,john_prefix.prefix) = '1'
> order by char_length(john_prefix.prefix) desc limit 1
>
> but when I execute it with this:
>
> UPDATE
> jpt_test
> set
> number = substring(number from length(john_prefix.prefix)+1)
> from
> john_prefix
> where
> prefix in (
> select prefix
> from john_prefix
> where strpos(jpt_test.number,john_prefix.prefix) = '1'
> order by char_length(john_prefix.prefix) desc limit 1
> ) ;
>
> table contents are as follows
>
> john_prefix table:
>
> prefix
> ---------
> 123
> 234
>
> jpt_test table:
>
> number
> -----------
> 1237999999
> 0234999999 <<< supposed to have no match
> 2349999999
>
> Am I missing something here? Any help will be appreciated.
I'm going to guess that it's because you didn't use a separate alias for the
FROM in the correlated subquery.
Doesn't STRPOS() return INTEGER, not TEXT?
--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg
From | Date | Subject | |
---|---|---|---|
Next Message | Rehan Saleem | 2012-01-30 06:42:51 | MS-SQL Store Procedure to Postgresql Function |
Previous Message | Tim Landscheidt | 2012-01-27 02:13:52 | Re: Getting a list of a table's attributes that are sortable sorted by uniqueness |