Didn't reply-all.... Begin forwarded message:
> From: David Johnston <pol...@yahoo.com> > Date: January 27, 2012 9:01:37 EST > To: John Tuliao <jptul...@htechcorp.net> > Subject: Re: [SQL] Query question > > On Jan 26, 2012, at 7:00, John Tuliao <jptul...@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.
