Updating tables

The examples below show how the row attribute can be used to overwrite or update the contents of table based columns:

<table name="ecollectionevents">
    <tuple>
        <atom name="irn">5000432</atom>
        <table name="LatSource_tab">
            <tuple>
                <atom>new source 1</atom>
            </tuple>
        </table>
    </tuple>
</table>

irn

LatSource_tab(1)

5000432

new source 1

As the row attribute value in the LatSource_tab column does not contain an update modifier (that is, +, - or =), the contents of the column are cleared before adding in the new data. The tables below show before and after cases for the above import:

 

Before

  

 

 After

1

source 1

  

1

new source 1

2

source 2

    

 

3

source 3

    

 

4

source 4

    

 

 
<table name="ecollectionevents">
    <tuple>
        <atom name="irn">5000432</atom>
        <table name="LatSource_tab">
            <tuple row="1">
                <atom>new source 1</atom>
            </tuple>
            <tuple row="3">
                <atom>new source 3</atom>
            </tuple>
            <tuple row="5">
                <atom>new source 5</atom>
            </tuple>
        </table>
    </tuple>
</table>

irn

LatSource _tab(1)

LatSource (3)

LatSource_tab(5)

5000432

new source 1

new source 3

new source 5

Once again as the first row attribute does not contain an update modifier, the contents of LatSource_tab will be cleared before the imported values are added. The use of the row attribute allows specific row positions to be set. The example below shows before and after cases of the above XML import:

 

Before

  

 

 After

1

source 1

  

1

new source 1

2

source 2

  

2

 

3

source 3

  

3

new source 3

4

source 4

  

4

 
     

5

new source 5

 
<table name="ecollectionevents">
    <tuple>
        <atom name="irn">5000432</atom>
        <table name="LatSource_tab">
            <tuple row="+">
                <atom>append source 1</atom>
            </tuple>
        </table>
    </tuple>
</table>

irn

LatSource__tab(+)

5000432

append source 1

As the row attribute value contains an update modifier, +, the existing contents of the LatSource_tab field are retained and added to:

 

Before

  

 

 After

1

source 1

  

1

source 1

2

source 2

  

2

source 2

3

source 3

  

3

source 3

4

source 4

  

4

source 4
     

5

append source 1

 
<table name="ecollectionevents">
    <tuple>
        <atom name="irn">5000432</atom>
        <table name="LatSource_tab">
            <tuple row="+">
                <atom>append source 1</atom>
            </tuple>
            <tuple row="+">
                <atom>append source 2</atom>
            </tuple>
            <tuple row="+">
                <atom>append source 3</atom>
            </tuple>
        </table>
    </tuple>
</table>

irn

LatSource_tab(+)

LatSource_tab(+)

LatSource_tab(+)

5000432

append source 1

append source 2

append source 3

Since the first row attribute contains an update modifier (the + modifier), the imported data updates the existing table values. Notice the use of the append modifier for each row to be appended:

 

Before

  

 

 After

1

source 1

  

1

source 1

2

source 2

  

2

source 2

3

source 3

  

3

source 3

4

source 4

  

4

source 4
     

5

append source 1
     

6

append source 2
     

7

append source 3

 
<table name="ecollectionevents">
    <tuple>
        <atom name="irn">5000432</atom>
        <table name="LatSource_tab">
            <tuple row="-">
                <atom>prepend source 1</atom>
            </tuple>
        </table>
    </tuple>
</table>

irn

LatSource_tab(-)

5000432

prepend source 1

Inserting a value into the first position in a table column is similar to appending, except that the prepend row operator (-) is used. When the value is inserted, existing values are moved down the table:

 

Before

  

 

 After

1

source 1

  

1

prepend source 1

2

source 2

  

2

source 1

3

source 3

  

3

source 2

4

source 4

  

4

source 3
     

5

source 4

 
<table name="ecollectionevents">
    <tuple>
        <atom name="irn">5000432</atom>
        <table name="LatSource_tab">
            <tuple row="2=">
                <atom>replace source 1</atom>
            </tuple>
            <tuple row="3=">
                <atom>replace source 2</atom>
            </tuple>
            <tuple row="6=">
                <atom>replace source 3</atom>
            </tuple>
        </table>
    </tuple>
</table>

irn

LatSource_tab(2=)

LatSource_tab(3=)

LatSource_tab(6=)

5000432

replace source 1

replace source 2

replace source 3

The = row operator is used to replace the contents of a row in a table. If the row does not exist, it is created and the appropriate value set:

 

Before

  

 

 After

1

source 1

  

1

source 1

2

source 2

  

2

replace source 1

3

source 3

  

3

replace source 2

4

source 4

  

4

source 4
     

5

 
     

6

replace source 3

 
<table name="ecollectionevents">
    <tuple>
        <atom name="irn">5000432</atom>
        <table name="LatDeterminedByRef_tab">
            <tuple row="+">
                <atom name="NamFirst">firstname</atom>
                <atom name="NamLast">lastname</atom>
            </tuple>
        </table>
    </tuple>
</table>

irn

LatDeterminedByRef_tab(+).NamFirst

LatDeterminedByRef_tab_tab(+).NamLast

5000432

firstname

lastname

All the update modifiers available for tables of values may be used for tables of references. For CSV data sources, columns with the same name and row attribute are handled as one update. For example, the CSV row attributes above both use the append operator (+). This does not result in two new rows, rather the same row is used to contain the reference:

 

Before

  

 

 After

1

reference 1

  

1

reference 1

2

reference 2

  

2

reference 2

3

reference 3

  

3

reference 3

4

reference 4

  

4

reference 4
     

5

append reference 1