Subscribe to this thread
Home - General / All posts - Update with join
sknox79 post(s)
#09-Feb-21 12:20

Hi, I'm back after quite a long absence, and mostly enjoying using 9.

The Update with join syntax is a little different to what I am used to. Looking at other forum posts I have come up with the following:

UPDATE (

SELECT a.[mfd_id], a.[Geom], b.[Acquired Type]

FROM [Gross Surplus Land Filtered] as a

INNER JOIN [Gross Surplus Land] as b

ON GeomContains(b.[Geom], a.[Geom], 0)

)

SET a.[Acquired Type] = b.[Acquired Type]  

My schema is

mfd_id int64

Geom geom

[Acquired Type] varchar

mfd_id_x btree

Shape_x rtree

for both source and destination tables.

The select query runs fine, and is the output I want to get, but when running the update I get Cannot parse query.

I've read some threads copying across indexes and so on, but I would expect a different error message if that were the case.

Could this be clarified a bit more in the manual, given every database implements it slightly differently and I don't think it's in any SQL standard?

tjhb
10,094 post(s)
#09-Feb-21 14:05

It's not complicated in this case.

The target field a.[Acquired Type] is not listed in the target table (not returned by SELECT).

After fixing that, I think you will also have to alias the fields a.[Acquired Type] and b.[Acquired Type] so that they have distinct names.

sknox79 post(s)
#09-Feb-21 15:37

Progress, thanks. So if I run

 Update (

select a.mfd_id, FIRST(b.mfd_id) as mfd_id2, a.[Geom], FIRST(b.[Geom]as geom2, FIRST(b.[Acquired Type]as at, a.[Acquired Type] as at2

FROM [Gross Surplus Land Filtered] as a

INNER JOIN [Gross Surplus Land] as b

ON GeomContains(b.[Geom], a.[Geom], 0)

GROUP BY a.mfd_id, a.[Acquired Type], a.[Geom]

Set at2 = at

I now get 'at2': Cannot set value. So the table names were causing problems, at least I can now run the query without a syntax error.

With the group by it should now be a 1:1 relationship, so still confused why it doesn't run.

tjhb
10,094 post(s)
#09-Feb-21 15:45

Yes. Now, using an an aggregate, the link to the original table(s) is lost The target given by the SELECT is no longer the original table(s), but a new table created inside the query.

This requires special handling. More soon.

There's another issue as well. With or without the aggregate, the join means the BTREE index on a.mfd_id is lost. (You can see this if you inspect the schema of the table produced by SELECT.) Again, special handling. Back soon.

sknox79 post(s)
#09-Feb-21 21:39

This worked:

Update (

select 

  a.[mfd_id] as tkey0,

  a.[Acquired Type] as at2,

  s.[Acquired Type] as at,

  s.s0 -- not needed for join, just info

FROM [Gross Surplus Land Filtered Drawing] as a 

LEFT JOIN 

SELECT 

[s_mfd_id] AS skey0, 

FirstNonNull([o_Acquired Type]AS [Acquired Type],

FirstNonNull([o_mfd_id]AS s0 -- not needed for join, just info

FROM CALL GeomOverlayContainingPar([Gross Surplus Land Filtered Drawing],[Gross Surplus Land Drawing], 0, ThreadConfig(SystemCpuCount()))

GROUP BY [s_mfd_id] 

as s 

ON a.[mfd_id] = s.skey0

Set at2 = at

Bit confused about the difference between GeomContains and GeomOverlayContaining, especially as there is no corresponding GeomContained, but seems like the Overlay functions are the way to go and are quicker.

sknox79 post(s)
#26-Feb-21 13:19

I really must have a mental block on this. Why does this fail with a Table must contain identity index. error?

UPDATE (

    SELECT orig.mfd_id, 

    jt.s0, 

    jt.t0,

    jt.skey0

    FROM [Rural_Urban_Classification_(2011)_of_LSOA] as orig

    INNER JOIN (

        SELECT a.mfd_id as skey0,

        b.mfd_id as s0,

        a.rur_value AS t0

        FROM [Rural_Urban_Classification_(2011)_of_LSOA] as a

        INNER JOIN [Rur_Urban_Score] as b

        ON a.[RUC11] = b.[field_nm]

        ) as jt

    ON orig.mfd_id = jt.skey0

)

SET t0 = s0

The table between the update brackets runs fine and has 4 columns as expected. And the table has primary keys on both sides of the join from what I can see. I think it's just the my mental model of what is actually being updated that is not quite right. But equally I think the docs could explain it better as this is quite critical functionality if you want to work in SQL.

sknox79 post(s)
#26-Feb-21 13:43

I was over-complicating this:

UPDATE [Rural_Urban_Classification_(2011)_of_LSOA]

SET [rur_value] = 

 (select mfd_id

   FROM [Rur_Urban_Score]

 WHERE [Rural_Urban_Classification_(2011)_of_LSOA].RUC11 = [Rur_Urban_Score].field_nm

);

I guess I just don't quite fully understand where theUPDATE () SETset syntax is required compared to this simpler update syntax that I am used to? Or is it just a style thing?

When you use UPDATE () SET it appears to create a new table, so it's unclear (to me at least) how that is actually updating the original table.

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