[postgis-users] How to use UPDATE command with LIKE clause?

[KhunSanAung]

Hi Hug,

That is helpful for me. Correction to the above command is;
UPDATE agriculture SET
geom = towns.geom
FROM towns
WHERE agriculture."code" LIKE towns."code"||'%';

Many thanks again & best regards

On Wed, Apr 29, 2015 at 5:42 PM, Hugues François <hugues.francois at irstea.fr>
wrote:

> Hello,
>
> It sounds more like a sql question than a postgis one but I think
> concatenate should helps:
>
>
> UPDATE public.agriculture SET
>         geom = towns.geom
>         FROM towns
> FOR EACH ROW
> WHERE agriculture."code" LIKE towns."code"||'%';
>
> HTH
>
> Hug
>
>
>
> -------- Message original --------
> De : KhunSanAung <khunsanaung.gis at gmail.com>
> Envoyé : Wednesday, April 29, 2015 10:37 AM
> À : postgis-users at lists.osgeo.org
> Sujet : [postgis-users] How to use UPDATE command with LIKE clause?
>
> Hi All,
>
> I've two tables with similar columns storing text values.
> agriculture (name, code, geom)
> town (name, code, geom)
>
> I'd like to run the UPDATE geom column for a string columns.
>
> UPDATE public.agriculture SET
> geom = towns.geom
> FROM towns
> FOR EACH ROW
> WHERE agriculture."code" LIKE towns."code";
>
> ​The problem here is the values of 'code' columns​ are not always exactly
> the same.
> agriculture.code sometimes contains additional text
> So I've to use the LIKE command. e.g.
> town.code = 'MMR013025'
> agriculture.code = 'MMR013025*701*'
>
> ​in that case I also like to have the values from towns aslo.​
>
> ​​UPDATE public.agriculture SET
> geom = towns.geom
> FROM towns
> FOR EACH ROW
> WHERE agriculture."code" LIKE '*MMR013025*%'
> ​
> What would be the right command ​to do it?
>
> Thank you very much in advance.
>
> --
> Have a nice day!
> --
>
> *Mr. Khun San Aung*
> * <http://geoportal.icimod.org/>*
>



-- 
Have a nice day!
--

*Mr. Khun San Aung*
* <http://geoportal.icimod.org/>*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150430/39d6953f/attachment.html>