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

[Hugues François]

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/>*