Fwd: [SQL] Query question

From: David Johnston <polobo@yahoo.com>
Date: January 27, 2012 9:01:37 EST
To: John Tuliao <jptuliao@htechcorp.net>
Subject: Re: [SQL] Query question

On Jan 26, 2012, at 7:00, John Tuliao <jptuliao@htechcorp.net> 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.

Regards,

JPT

Your double-use of john_prefix is problematic; combined with the use of a sub-query in the where clause.  When you use from with update you need to specify how the from table and the update table are related - you have not done this since the sub-query from reference is not the same as the from clause table reference.

David J.