How to update a field in one geodatabase table via a join to another geodatabase table?

How to update a field in one geodatabase table via a join to another geodatabase table?

I have 2 tables in a file geodatabase and I need to update a field (via ArcObjects) in the first table with a field in the first. Basically, the equivalent to SQL Server's update statement:

UPDATE t1 SET t1.DacCode = t2.NewDacCode FROM Table1 as t1 INNER JOIN Table2 t2 ON t1.PID = t2.PID ;

I would also be curious as to accomplish this sort of update (if different from a direct set of another field value):

UPDATE t1 SET t1.DacCodeDesc = Concat(t2.NewDacCode,'_1201') FROM Table1 as t1 INNER JOIN Table2 t2 ON t1.PID = t2.PID ;

I currently have a IQueryDef that returns a cursor of what I want to update but that's read-only. So I'm hoping there's a way to do it that is faster thanIRow.Store.

Datasets are the primary mechanism to provide access to geographic information in ArcGIS. The geodatabase contains the following primary dataset types:

Creating a collection of these dataset types is the first step in designing and building a geodatabase. Once you create these, you can add to or extend your geodatabases with more advanced capabilities (such as by adding topologies, networks, or subtypes) to model GIS behavior, maintain data integrity, and work with an important set of spatial relationships.

An overview of the Data Management toolbox

The Data Management toolbox provides a rich and varied collection of tools that are used to develop, manage, and maintain feature classes, datasets, layers, and raster data structures.

The 3D Objects toolset contains tools for enhancing a multipatch feature class with the capabilities of the 3D object feature layer. The 3D object feature layer improves multipatch features by optimizing the storage of texture information and adding support for additional visualization effects, textures, and materials associated with several 3D modeling formats.

The Archiving toolset contains tools for managing geodatabase archiving. Archiving in ArcGIS provides the functionality to record and access changes made to data in a geodatabase. Geodatabase archiving is the mechanism for capturing, managing, and analyzing data change.

The Attachments toolset contains tools for managing geodatabase attachments. The attachments functionality provides a powerful way to associate nongeographic data with your geographic information. With these geoprocessing tools you can more easily incorporate attachments into your workflows and automate the process of adding and removing attachments.

The Attribute Rules toolset contains tools for managing geodatabase attribute rules. Attribute rule functionality provides a way to add custom rules and behaviour to your geodatabase data. With these geoprocessing tools you can easily incorporate attribute rules into your workflows and automate the process of adding and removing rules.

The Contingent Values toolset contains tools for managing geodatabase contingent values and field groups. Contingent values functionality provides a way to add custom behavior to your geodatabase data. You can incorporate contingent values into your workflows and automate the process of adding and removing values using these tools.

The Data Comparison toolset contains tools to compare one dataset with another dataset to report similarities and differences. You can use these tools to identify changes made to a dataset.

The Distributed Geodatabase toolset contains tools that allow you to create and synchronize changes between replica geodatabases.

Domains offer a way to define a range of values that can be used for multiple attribute fields. Using domains helps ensure data integrity by limiting the choice of values for a particular field. Validation for coded value domains is accomplished by restricting field values found in drop-down lists. Range domains are automatically validated during editing.

The Feature Binning toolset contains tools for managing feature binning. Feature binning is an advanced visualization capability that allows users to explore and visualize large datasets. It also helps users observe patterns at macro and micro levels with simple out-of-the-box mapping options.

The Feature Class toolset provides a set of tools that perform basic feature class management.

The Features toolset contains tools to create and manage feature-based GIS data, transform features from one geometry type to another, find and correct issues with feature geometry, and record feature geometry measurements and coordinates as attributes.

The Fields toolset contains a set of tools to create and modify fields. A field is a column in a table each field contains the values for a single attribute. Any number of fields can be included in a table. Settings can be specified for fields, such as the field type and the maximum size of the data that can be stored in it.

To reduce storage requirements, you can compress vector file geodatabase feature classes and tables to a read-only format. Files can reduce to as little as 1/4 their original size. Once compressed, a dataset looks the same in ArcCatalog and ArcMap as when it was uncompressed. Also, apart from editing, you work with it the same way. The compressed data is a direct access format, so you do not have to uncompress it each time you access it: ArcGIS and ArcReader read it directly.

The General toolset contains tools for performing general data management operations. The toolset contains tools for combining feature classes or tables renaming, copying, or deleting datasets finding or deleting duplicate or identical records and sorting records.

Tools in the Generalization toolset can be used to aggregate or eliminate features.

The Geodatabase Administration toolset contains tools for a variety of geodatabase management tasks. Specific geodatabase data management tools as well as geodatabase upgrade, management, and maintenance are covered by the tools in this toolset.

The Indexes toolset provides tools that create and remove indexes.

Tools in the Joins and Relates toolset create a join or a relate between a layer and a table view . Joins made with the Add Join tool or relates made with the Add Relate tool are temporary and do not permanently transfer the fields between tables. If you need to preserve the join or relate between sessions, you can create a relationship using the tools in the Relationship Classes toolset.

The LAS Dataset toolset provides tools for creating, managing, and optimizing an LAS dataset. The LAS dataset data type references collections of LAS files, making them easier to process, visualize, and analyze.

The Layers and Table Views toolset creates and manipulates layers, layer files, and table views.

The Package toolset provides tools to consolidate, package, and share layers ( .lyr and .lyrx ), maps ( .mxd and .mapx ), projects ( .aprx ), address locators ( .loc ), and geoprocessing results.

The Photos toolset contains tools for analyzing and managing digital photograph files. The toolset includes tools for creating a point feature class from geotagged photo files (photos that were captured from a GPS camera device or a smartphone) and matching photo files to features or table rows based on the time stamp of the photo.

This toolset contains tools to convert geographic data from one map projection to another. There are additional tools for transforming raster datasets, such as shift, rescale, and rotate.

The Raster toolset in the Data Management toolbox provides tools that perform raster data management and raster processing. These tools allow you to work with raster dataset properties and create and process raster data.

Relationship classes define relationships between objects in the geodatabase. These relationships can be simple one-to-one relationships, similar to what you might create between a feature and a row in a table, or more complex one-to-many (or many-to-many) relationships between features and table rows. Some relationships specify that a given feature, row, or table is not only related to another feature but that creating, editing, or deleting one will have a specified effect on the other. These are called composite relationships, and they can be used to ensure that the links between objects in the database are maintained and up to date. Deleting a feature, such as a power pole, can trigger the deletion of other features, such as a transformer mounted on the pole or the maintenance records in a related table.

The Sampling toolset provides tools that create features used either as sampling locations or as aggregation areas. For example, the Create Random Points tool creates points that can be used as sampling locations within the extent of a dataset. The Generate Tessellation tool creates a mesh of triangle, square, or hexagon polygons within an extent that can be used to aggregate other data.

One of the great advantages of using the geodatabase to store geographic data is the ability to create subtypes for your features and attributes. Subtypes provide a method of dividing your feature classes or tables into logical groupings based on an attribute value. By allowing you to work with a subset of features in a feature class, subtypes make it possible to assign consistent attributes and behavior to those subsets.

A table contains a collection of rows and columns, where each row, or record, represents a geographic feature—such as a parcel, power pole, highway, or group of lakes—and each column, or field, describes a particular attribute of the geographic feature, such as its square footage, height, length, and area. Tables are typically stored in a database or simply in files such as dBASE tables. Tables manage the attributes of geographic features.

The Tile Cache toolset contains tools to generate, manage, import, and export tile caches. These tools are capable of generating tile caches from a raster dataset or a mosaic dataset on your desktop without the need for ArcGIS Server . These tile caches can then be shared as tile packages on ArcGIS Online and published as tiled map services.

The Topology toolset contains a set of tools that can be used to create and manage a geodatabase topology.

Versioning allows multiple users to access geographic data in a geodatabase. Versioning lets connected users simultaneously create multiple, persistent representations of the geodatabase without data duplication. The same features or rows can be concurrently edited without explicitly applying locking the data. This framework lets you create versions of a geodatabase for the states of a project, reconcile differences between versions, and update the master version of a geodatabase with the design as-built. Versioning is supported by the Enterprise geodatabase only.

The Workspace toolset contains a set of tools to create the data storage structures used by ArcGIS.

2 Answers 2

Try setting out_dataset before entering the loop (immediately after tableList = arcpy.ListTables() and see if it gets past the "already exists" error.

to follow on @Evan, you could erase the table if it pre-exists.

Also, if I know I'm going to be recreating a table over and over and I don't want to write checks like this into my code, I use:

If this table type isn't really recognized by arcpy, then you could do an analogous "if-exists" thing w/just python's os module or something else along those lines.

Features added throughout prior releases

  • supportedQueryFormats now includes pbf (protocol buffer), a compact binary encoding for geographic data.
  • A new property named archivingInfo is added.
  • The supportsCountDistinct property is added inside the advancedQueryCapabilities element.
  • A new property named hasMetadata is added. This value is true when the service is published using ArcGIS Pro 2.2 or later, false otherwise.
  • If hasMetadata is true, the layer / table supports the iteminfo , thumbnail , and metadata resources.
  • A new property named displayFieldExpressionInfo is added it returns the tile of the expression and an Arcade expression.
    • When a layer is authored with displayExpression , its resources still report displayField , which is a field picked from the Arcade expression or the default displayField in case the expression does not have any field in it.
    • returnDomainNames is a parameter on the layer resource where domain information includes only the domain name. The domain names can be matched with the full domain information as returned by the queryDomains operation. This approach is a more efficient way to work with domains, as it requires returning the full domain information only once instead of with each layer.
    • Layer reports its sourceSpatialReference , which can be different than what is in its extent or its container map service's spatial reference.
    • Added the following new flags:
      • supportsDatumTransformation indicates the layer supports datum transformation while query results get projected.
      • supportsSqlExpression indicates that the layer/table supports sql expressions in query operation's orderBy , groupByFieldsForStatistics , and outStatistics .
      • rangeInfos returns a list of available ranges including their names, current values, and full extent.
      • parameterInfos returns a list of available parameterized filters. These are preauthored filters to limit what you want to query or display. This is like layerDefs except that layerDefs requires you to pass in an entire sql where clause whereas for parameterized filters, you need to pass in the name-value pair.
      • supportsStatistics supports Query Layers, for example, such as layers / tables defined by a SQL query from a database, geodatabase archived layer, and so on.
      • Layers from file geodatabases support pagination ( supportsPagination property). Other supported workspaces include Altibase, Hana, Oracle 12c (and later releases), PostgreSQL, SQLite, and SQL Server 2012 (and later releases). The following are not supported: DB2, Informix, Netezza, and Teradata.
      • All properties related to query are grouped inside advancedQueryCapabilities .
      • A new property is reported to indicate whether the layer supports pagination in a query result— supportsPagination .
      • supportsTrueCurve is always true and indicates whether the service supports accepting and returning true curves as a segment of a polyline or polygon.
      • supportsAdvancedQueries is split into supportsOrderBy and supportsDistinct .
      • A new property is reported to indicate whether the layer requires the use of standardized queries— useStandardizedQueries . A value of true indicates the layer requires the use of standardized queries, a value of false indicates otherwise. Learn more about standardized queries.
      • Layer / table resources no longer report the existing definition expression set in the source map document.

      10.1 SP1

      • If ownership-based access control is enabled on features, the ownershipBasedAccessControlForFeatures property of a layer will include an allowOthersToQuery property.

      How to open a GDBTABLE file

      You need a suitable software like ArcGIS to open a GDBTABLE file. Without proper software you will receive a Windows message "How do you want to open this file?" or "Windows cannot open this file" or a similar Mac/iPhone/Android alert. If you cannot open your GDBTABLE file correctly, try to right-click or long-press the file. Then click "Open with" and choose an application. You can also display a GDBTABLE file directly in the browser : . Just drag the file onto this browser window and drop it.

      Sources of tabular information

      There are lots of sources of tabular data, and ArcGIS can take advantage of many formats. Tabular information could be stored as tables in folders or databases, text files, queries on databases, and so on. In addition, if you have spatial data, you probably already have tabular attributes that describe those geographic features.

      File-based tables are stored in folders on disk. Some examples of file-based sources of tabular information include the following:

        , the format used with shapefiles , the format used with coverages
    • Text files, such as those created in a text editor and delimited by commas or tabs
    • Many other sorts of tables, including those generated in other programs, such as Microsoft Excel, either accessed directly in ArcGIS or through the OLE DB functionality
    • Tables in a database or geodatabase can contain some types of information that file-based tables do not support. For example, database or geodatabase tables can store BLOB or raster field types. In addition, databases and geodatabases provide capabilities to extend the functionality of tables, such as maintaining data integrity and managing transactions.

      You can perform queries on these database or geodatabase tables to create new tables. The Make Query Table tool, for example, allows you to apply a SQL expression to one or more tables. The query can be used to join the tables or return a subset from the original data.

      To learn more about what you can do with these different kinds of tables, see About tabular data sources.

      Introducing Geographic Information Systems with ArcGIS: A Workbook Approach to Learning GIS, 3rd Edition

      Introducing Geographic Information Systems with ArcGISintegrates a broad introduction to GIS with a software-specific workbook for Esri's ArcGIS. Where most courses make do using two separate texts, one covering GIS and another the software, this book enables students and instructors to use a single text with an integrated approach covering both in one volume with a common vocabulary and instructional style.

      This revised edition focuses on the latest software updates&mdashArcGIS 10.0 and 10.1. In addition to its already successful coverage, the book allows students to experience publishing maps on the Internet through new exercises, and introduces the idea of programming in the language Esri has chosen for applications (i.e., Python). A DVD is packaged with the book, as in prior editions, containing data for working out all of the exercises.

      This complete, user-friendly coursebook:

      • Is updated for the latest ArcGIS releases&mdashArcGIS 10.0 and 10.1
      • Introduces the central concepts of GIS and topics needed to understand spatial information analysis
      • Provides a considerable ability to operate important tools in ArcGIS
      • Demonstrates new capabilities of ArcGIS 10.0 and 10.1
      • Provides a basis for the advanced study of GIS and the study of the newly emerging field of GIScience

      Introducing Geographic Information Systems with ArcGIS, Third Edition is the ideal guide for undergraduate students taking courses such as Introduction to GIS, Fundamentals of GIS, and Introduction to ArcGIS Desktop. It is also an important guide for professionals looking to update their skills for ArcGIS 10.0 and 10.1.

      Geodatabase support

      Mobile service and mobile cache created by the Create Mobile Cache tool, ArcGIS for Windows Mobile supports editing of data within file-based and multiuser ArcSDE geodatabases. Your mobile map can contain layers that reference other data sources, but to make a layer editable, its data source must at least be a feature class stored within a geodatabase. For more details about the requirements of an editable layer, read the Data schema requirements for purposes of operational layers section below.

      Also note that a feature class cannot carry field names that are reserved by the SQL language.

      A hosted feature service has less restrictions than a mobile service in terms of requirements to the data source. Your data can come from a file-based geodatabase, an ArcSDE geodatabase, CSV, or shape files. Data from these sources is editable by the field applications if you publish it as hosted feature services. However, when a set of data is published as a hosted feature service, the data is copied to the ArcGIS Online for organizations or Portal for ArcGIS online storage therefore, any edits made by field crew will not go back to the source database, but to the online storage.

      Tasks you can perform with tables and attribute information

      There are many mapping, analysis, and data management tasks you can perform using tabular data.

      Tables allow you to map and visualize your data. For example, you can classify or categorize attributes to symbolize a layer. You can use population values to symbolize major cities with a larger symbol than would be used for smaller towns and villages. You can also specify that a different color be used to represent each type of land use in a parcel layer. In addition, you can use the attribute values to generate text to label each parcel feature. In the graphic below, the parcels are symbolized by the type of land use, then labeled with their parcel ID values.

      Attribute data helps you perform spatial queries and analyses. For example, you can examine the distribution of features with certain attributes by using ArcMap to select the features that contain attributes you want to examine. In the graphic below, the features with a LAND_USE value of UNK (unknown) are selected using an attribute query.

      When information in your geodatabase or database changes, you can update your attributes. For example, you'll need to update your database when land use or property ownership changes or the unknown values are classified. If you have a feature class representing some pipes with a field for the diameter, you can easily change the attributes when the crew removes an 8-inch pipe and replaces it with a 6-inch pipe. You can edit tabular values within either the Table window or the Attributes window, which shows attributes of only individual, selected features.

      The geodatabase includes functionality that allows you to enhance, maintain, and enforce the integrity of your tabular data. For example, by establishing attribute domains, you can set up rules that specify the valid values for the records in your table. So when updating the pipe diameter attributes, you can use attribute domains to ensure that the diameter is appropriate for that section of pipe. Range domains, for example, ensure that the values you enter are within a valid range. The graphic below shows the use of coded value domains when editing, which allows you to choose a value from a predefined list and avoid making typographic errors.

      ArcGIS also allows you to convert data in a table into spatial data. For example, a commonly converted data source is a list of coordinates obtained from using a GPS unit in the field. You can easily add such x,y data to ArcMap to display it.

      The graphics below show a text file containing the x,y locations of hydrants (top graphic) and the points displayed in ArcMap on the map and in the Table window (bottom graphic).

      If you have a list of addresses, you can use geocoding to match them to known street locations to create point features. In addition, through linear referencing, you can indicate events along line features with just an identifier and a location.

      Tables are also at the root of data models, which are templates that you can use to set up your geodatabase to better model real-world phenomena.

      Watch the video: ArcMap - Table join