Subscribe to this thread
Home - General / All posts - M9 - update a table from a relationship with another table
artlembo


3,216 post(s)
#25-Jul-21 20:33

I am trying to update a table (tcsoils) with the results of a query:

codeUPDATE 

 (SELECT tcsoils.musym, t1.pComp, tcsoils.primarycomp

  FROM [soils.gdb]::[tcsoils],

   (SELECT mukey, first(sumpct) AS comppct,

      first(compname) AS pComp

    FROM 

 (SELECT mukey,[compname],sum([comppct_r]) AS sumpct

      FROM [soils.gdb]::[main_component]

      GROUP BY mukey, [compname]

    ORDER BY mukey, sumpct DESC)

 GROUP BY mukey) AS t1

  WHERE t1.mukey = tcsoils.mukey)

SET primarycomp = pComp

the error I am getting is Table must contain identity index. The problem I think is that the inner query isn't a table, but rather the results of a query, and therefore doesn't have an identity index. I really would like to do this on-the-fly, and not have to write the inner query to a permanent table. SQLServer and Postgres don't seem to require an identity index, so I'm not sure if there is another way of writing this.

steveFitz

326 post(s)
#25-Jul-21 23:49

Punching above my weight here but, I did struggle with an udate query with the same error and finally tried a simplified version with most being after the SET and it worked (thanks to a post by sknox!). Just not sure why. See http://www.georeference.org/forum/t155112.7#155310

tjhb

9,852 post(s)
#26-Jul-21 09:10

You might be thinking too hard Art.

The result of the outer SELECT is a table (albeit a virtual one, aka a view) but the error message is right.

UPDATE requires that the target table have an identity index--a BTREE--on at least one of the listed fields.

That is not the case with your target table. (You can confirm that by running everything inside the UPDATE and pressing Ctrl-E.)

It is difficult to do with the result of a join, as you must ensure a priori, logical uniqueness (in other words, exclude any logical possibility of multiple matches, regardless of actual data) to preserve any source BTREE--the best way to do that is often a join back with a wrap, using an aggregate like FIRST--or otherwise write your own new BTREE on the result, possibly on a temporary table.

tjhb

9,852 post(s)
#26-Jul-21 20:43

The FIRST... over ORDER BY... should also be replaced by a COLLECT expression, to ensure determinate ordering. (FIRST and LAST effectively mean ANY in M9.)

Are the mukey fields unique (and indexed with BTREE) in both tables; otherwise are there some other fields for which that is true?

lionel

800 post(s)
#10-Oct-21 19:25

A) Does a natural ordering make sense in GUI way relative to FIRST and LAST ?

when table is open it seem we have the natural order .

When table is order in gui way a arrow appear in column name but no way to come back to unordered.

We have to close and re open table !!

B) Is there a way to show/Highlight the selected row in "rectangle Table" when use SQL code ?

ORDER BY (manifold.net)

Attachments:
ordre.map
ordre_table_cursorOrderonTop.png


union, doc , APIand most important deepl & keyboard shortcut

Dimitri


6,803 post(s)
#11-Oct-21 09:16

A) Does a natural ordering make sense in GUI way relative to FIRST and LAST ?

No, not when tables are unordered. There is no order in tables. Assuming that is a mistake. When you want to order the result of a SELECT, use ORDER BY.

See the discussion in the How Records are Ordered for Display section of the Tables topic.

when table is open it seem we have the natural order .

It may seem that way, but expecting any particular order is a mistake.

but no way to come back to unordered.

"Unordered" means just that. "back to unordered" means "show records in random order." Why do you want to show them in random order?

As a practical matter, you could always sort by the mfd_id field to get what is likely close to the initial display when you first open a table. But there's no guarantee that every time you open a table window you'll get records in the same order. If you want them in the same order, sort by a field or use a query with ORDER BY.

Is there a way to show/Highlight the selected row in "rectangle Table" when use SQL code ?

Use the Select pane with an expression.

lionel

800 post(s)
#11-Oct-21 10:46

1) I was aware about mfd_id after test.

If i have mfd_id of value 1 , 2,3,4 and delete row with mfd_id =2. Then if i add a new record, the value of 2 will be use.Manifold doc has a page about computed field about date for store creation date ...Manifold remove all default M8 intrinsic fields in M9 because all can be achieve in M9 with "computed column" using schema and SQL.

2)Does custom C# and SQL function can be use ( trigger) when use schema for create column?

3) Does all type of column have there own default order ?

- text ( nvarrchar) order is about alphabet ABC

- number order is about mathématic/logic/time/size (1=true)

-geom .....boundingbox area? (Point =0)

-tile............size

I will test


union, doc , APIand most important deepl & keyboard shortcut

Dimitri


6,803 post(s)
#11-Oct-21 12:05

Does all type of column have there own default order ?

No. Please read the How Records are Ordered for Display section of the Tables topic for useful details.

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