Thread: Fwd: [SQL] Query question
Didn't reply-all....
Begin forwarded message:
Begin forwarded message:
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 prefixfrom john_prefixwhere strpos(jpt_test.number,john_prefix.prefix) = '1'order by char_length(john_prefix.prefix) desc limit 1but when I execute it with this:UPDATEjpt_testsetnumber = substring(number from length(john_prefix.prefix)+1)fromjohn_prefixwhereprefix in (select prefixfrom john_prefixwhere strpos(jpt_test.number,john_prefix.prefix) = '1'order by char_length(john_prefix.prefix) desc limit 1) ;table contents are as followsjohn_prefix table:prefix---------123234jpt_test table:number-----------12379999990234999999 <<< supposed to have no match2349999999Am 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.