Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id E8E021B76BD5
	for <pgsql-sql@postgresql.org>; Thu, 26 Jan 2012 08:00:57 -0400 (AST)
Received: from cluster1.pc3ph.com ([202.124.134.2])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jptuliao@htechcorp.net>) id 1RqO0b-0006Uw-Ak
	for pgsql-sql@postgresql.org; Thu, 26 Jan 2012 12:00:56 +0000
Received: from [172.16.10.215] (intra.eglobalreach.net [202.124.138.210])
	by cluster1.pc3ph.com (8.14.3/) with ESMTP id q0QC0b49030819
	for <pgsql-sql@postgresql.org>; Thu, 26 Jan 2012 20:00:37 +0800
Message-ID: <4F214065.8080201@htechcorp.net>
Date: Thu, 26 Jan 2012 20:00:37 +0800
From: John Tuliao <jptuliao@htechcorp.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.24) Gecko/20111108 Thunderbird/3.1.16
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Query question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201201/108
X-Sequence-Number: 36279

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.

Regards,
JPT