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