Skip to content
≡

Administrator

Developer

Tools

External Tools

MOVE

MOVE#

Syntax#

move ::=#

move_image238

where_clause ::=, limit_clause::=

column_commalist ::=#

column_commalist_image239

expression_commalist ::=#

expression_commalist_image240

Prerequisites#

In order to move table records, it is necessary to have both DELETE privileges for the table from which the records are to be moved and INSERT privileges for the table to which the records are to be moved. This is because moving data involves both inserting and deleting data.

In order to insert records into the table specified in the INTO clause, it is necessary to be the SYS user or the owner of the schema containing the table, to have the INSERT ANY TABLE system privilege, or to have the INSERT privilege for the table.

In order to delete records from the table specified in the FROM clause, it is necessary to be the SYS user or the owner of the schema containing the table, to have the DELETE ANY TABLE system privilege, or to have the DELETE privilege for the table.

Description#

The MOVE statement is used to move data that satisfy certain conditions from one table to another. It is also possible to move data to a specified partition.

hints#

The use of hints is supported with the FROM clause, and is the same as when using hints in a SELECT statement.

source_tbl_name, target_tbl_name#

These are used to specify the names of the tables from and to which the data will be moved. They must not be views or meta tables.

column_commalist#

This is a list of actual columns belonging to the target table.

expression_commalist#

This is a comma-delimited list of expressions. Each expression can be a column belonging to the FROM table, a constant or an expression.

where_clause#

The structure of the WHERE clause is the same as that in a SELECT statement.

limit_clause#

The structure of the LIMIT clause is the same as that in a SELECT statement.

Considerations#

  • Data cannot be moved from one table to the same table.
  • When a partition is specified, a value that is inappropriate for the partition cannot be entered.
  • MOVE can fail due to the CHECK constraint.

Examples#

<Query> >Move all records that satisfy the condition (T2.I2 = 4) from columns I1 and I2 of table T2 into the corresponding columns in table T1 and delete the original records from table T2.

iSQL> MOVE INTO T1(I1, I2) FROM T2(I1, I2) WHERE T2.I2 = 4;

<Query> Insert records comprising columns I1, I2, and I3 from table T2 into table T1 and delete them from table T2. (Table T1 must have columns corresponding to columns I1, I2, and I3 in table T2, and must have the same number of columns, that is, three columns.)

iSQL> MOVE INTO T1 FROM T2(I1, I2, I3);