Getting Started

Documentation

Glish

Learn More

Programming

Contact Us

Version 1.9 Build 1556

News	FAQ
Search	Home

Subsections

• UPDATE
• INSERT
• DELETE

Modifying a table

Usually TaQL will be used to get a subset from a table. However, as described in the first sections, it can also be used to change the contents of a table using the UPDATE, INSERT, or DELETE command. Note that a table has to be writable, otherwise those commands exit with an error message.

UPDATE

  UPDATE table_list SET update_list [WHERE ...] [ORDERBY ...]
                                    [LIMIT ...] [OFFSET ...]

updates selected rows in a table.
update_list is a comma-separated list of column=expression parts. Each part tells to update the given column using the expression. Both scalar and array columns are supported. E.g.

  UPDATE vla.ms SET ANTENNA1=ANTENNA1-1, ANTENNA2=ANTENNA2-1

to make the antenna numbers zero-based if accidently they were written one-based.
If an array gets an array value, the shape of the array can be changed (provided it is allowed for that table column). Arrays can also be updated with a scalar value causing all elements in the array to be set to that scalar value.

It is also possible to update part of an array using array indexing. E.g.

  UPDATE vla.ms SET FLAG[1,1]=T
  UPDATE vla.ms SET FLAG[1,]=T

The first example sets only the first array element, while the second one sets an entire row in the array. The second example also shows that it is possible to set an array to a scalar value.

Type promotion and demotion will be done as much as possible. For example, an integer column can get the value of a double expression (which will be truncated).

Note that if multiple column=expression parts are given, the columns are changed in the order as specified in the update-list. It means that if an updated column is used in an expression for a later column, the new value is used when evaluating the expression. E.g. in

  UPDATE vla.ms SET DATA=DATA+1, SUMD=sum(DATA)

the SUMD update uses the new DATA values.

INSERT

The INSERT command adds rows to the table. It can take two forms:

  INSERT INTO table_list [(column_list)] VALUES (expr_list)
  INSERT INTO table_list [(column_list)] SELECT_command

The first form adds one row to the table and puts the values given in the expression list into the columns given in the column list. If the column list is not given, it defaults to all stored columns in the table in the order as they appear in the table description. Each expression in the expression list can be as complex as needed; for example, a subquery can also be given. Note that a subquery is evaluated before the new row is added, so the new row is not taken into account if the subquery is done on the table being modified.
It should be clear that the number of columns has to match the number of expressions.
Note that row cells not mentioned in the column list, are not written, thus may contain rubbish.
The data types of expressions and columns have to match in the same way as for the UPDATE command; values have to be promotable or demotable to the column data type.
For example:

  INSERT INTO my.ms (ANTENNA1,ANTENNA2) VALUES (0,1)

adds one row, puts 0 in ANTENNA1 and 1 in ANTENNA2.

The second form evaluates the SELECT command and copies the rows found in the selection to the table being modified (which is given in the INTO part). The columns used in the modified table are defined in the column list. As above, they default to all stored columns. The columns used in the selection have to be defined in the SELECT part of the SELECT command. They also default to all stored columns.
For example:

  INSERT INTO my.ms select FROM my.ms

appends all rows and columns of my.ms to itself. Please note that only the original number of rows is copied.

  INSERT INTO my.ms (ANTENNA1,ANTENNA2) select ANTENNA2,ANTENNA1
   FROM other.ms WHERE ANTENNA1>0

copies rows from other.ms where ANTENNA1>0. It swaps the values of ANTENNA1 and ANTENNA2. All other columns are not written, thus may contain rubbish.

DELETE

  DELETE FROM table_list
    [WHERE ...] [ORDERBY ...] [LIMIT ...] [OFFSET ...]

deletes some or all rows from a table.

  DELETE FROM my.ms WHERE ANTENNA1>13 OR ANTENNA2>13

deletes the rows matching the WHERE expression.
If no selection is done, all rows will be deleted.
It is possible to specify more than one table in the FROM clause to be able to use, for example, keywords from other tables. Rows will be deleted from the first table mentioned in the FROM part.