georeference.org
Subscribe to this thread
Home - Forum / All posts - udpate query with where touches
klausk108 post(s)
#19-May-16 19:24

Hallo,

I would like to make a query which selects all geoms starting with F in the field "BYKV_BTYP". They also must touches all geoms starting with O in the field "BYKV_BTYP". In the end the Field "FanO" should get the value 1.

code

UPDATE [Bio]

SET [FanO]=1

SELECT * FROM Bio, Bio as Veg

WHERE TOUCHES(Veg.ID,Bio.ID)

AND Veg.BYKV_BTYP Like"O%" AND Bio.BYKV_BTYP Like "F%"

danb


1,537 post(s)
#19-May-16 21:48

How about ...

UPDATE

    (SELECT [D1].*

    FROM [BIO] AS [D1]

    INNER JOIN [BIO] AS [D2]

    ON Touches([D1].[ID][D2].[ID])

    AND (UCase(Left([D2].[BYKV_BTYP], 1)) = "O" AND UCase(Left([D1].[BYKV_BTYP], 1)) = "F"))

SET [FanO] = 1;


Landsystems Ltd ... Know your land | www.landsystems.co.nz

tjhb
7,097 post(s)
#19-May-16 22:00

Nice Dan. Add selecting into the UPDATE too? You can add to any existing selection with the INNER JOIN, or ensure a new selection using LEFT JOIN followed by CASE on NOT NULL. A LEFT JOIN would in turn require filtering for the [FanO] column--setting a default value such as -1 or a slightly icky attempt to set to NULL would work (SET x = NULL is always safely ignored).

klausk108 post(s)
#20-May-16 20:36
klausk108 post(s)
#20-May-16 20:36

Hallo Dan,

thx. I only have to change a point to comma then then query runs fine.

codeUPDATE

    (SELECT [D1].*

    FROM [BIO] AS [D1]

    INNER JOIN [BIO] AS [D2]

    ON Touches([D1].[ID], [D2].[ID])

    AND (UCase(Left([D2].[BYKV_BTYP], 1)) = "O" AND UCase(Left([D1].[BYKV_BTYP], 1)) = "F"))

SET [FanO] = 1;

tjhb
7,097 post(s)
#21-May-16 00:04

Nicely spotted! Do you need the selection function as well?

Manifold User Community Use Agreement Copyright (C) 2007-2011 Manifold Software Limited. All rights reserved.