Fwd: [SQL] Query question

Didn't reply-all....

Begin forwarded message:

> From: David Johnston <polobo(at)yahoo(dot)com>
> Date: January 27, 2012 9:01:37 EST
> To: John Tuliao <jptuliao(at)htechcorp(dot)net>
> Subject: Re: [SQL] Query question
>
> On Jan 26, 2012, at 7:00, John Tuliao <jptuliao(at)htechcorp(dot)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.