Example 9-13 Update all of the rows

UPDATE employees SET salary = salary *1.1;

Example 9-14 Insert one row

INSERT INTO employees VALUES (102494, 'Jane Doe, ...
    );

Example 9-15 Delete one row

DELETE employees WHERE employee_id = 103678;

Example 9-16 DML referencing duplicated table

In this example, employees is a sharded table and ref_jobs is a duplicated table.

DELETE employees
            WHERE job_id IN (SELECT job_id FROM ref_jobs
                            WHERE job_id = 'SA_REP');

Example 9-17 DML referencing another sharded table

UPDATE departments SET department_name = 'ABC‘
            WHERE department_id IN (SELECT department_id
                                    FROM employees
                                    WHERE salary < 10000);

Example 9-18 Insert as select from a local table

INSERT INTO employees SELECT * FROM local_employees;

Example 9-19 DML affecting one shard

A DML statement might affect only one shard, or it can involve multiple shards. For example, the DELETE statement shown here affects only one shard because there is a predicate on the sharding key (employee_id) in the WHERE clause..

DELETE employees WHERE employee_id = 103678;

Example 9-20 DML affecting multiple shards

The following statement affects all of the rows in the EMPLOYEES table because it does not have a WHERE clause.

UPDATE employees SET salary = salary *1.1;

To run this UPDATE statement on all shards, the shard coordinator iterates over all of the primary shard databases and invokes remote execution of the UPDATE statement. The coordinator starts a distributed transaction and performs two phase commit to guarantee the consistency of the distributed transaction. If there is an in-doubt transaction, you must recover it manually.

Example 9-21 Merge statement with sharded table employees as the target table

In this example, the employee_id column is the sharding key, and the join predicate on the source query is on the sharding key, so the MERGE statement will get pushed to all of the shards to be executed.

MERGE INTO employees D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.salary = D.salary + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.salary)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

Example 9-22 Merge statement with duplicated table as the target table

In this example, the target table is the duplicated table ref_employees. The source query references the sharded table employees and the join predicate is on the sharding key employee_id.

MERGE INTO ref_employees D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.salary = D.salary + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.salary)
     VALUES (S.employee_id, S.salary*0.1)
      WHERE (S.salary <= 8000);