Re: [SQL] Query question

[Lew]

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql