agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: John Tuliao <jptuliao@htechcorp.net>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Query question
Date: Thu, 26 Jan 2012 20:00:37 +0800
Message-ID: <4F214065.8080201@htechcorp.net> (raw)

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

view thread (15+ messages)  latest in thread

Message-ID: <4F214065.8080201@htechcorp.net>
Permalink:  ../4F214065.8080201@htechcorp.net/
Also on:    postgresql.org/message-id/4F214065.8080201@htechcorp.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jptuliao@htechcorp.net
  Subject: Re: Query question
  In-Reply-To: <4F214065.8080201@htechcorp.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox