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.
|