MaxCompute - MaxCompute SQL Supports the UPDATE, DELETE, and MERGE INTO Statements (Public Preview)
Target customers: developers, analysts, and users who want to modify or delete data in data warehouses. Features released: MaxCompute now supports the UPDATE and DELETE statements that allow you to manage data in tables or partitions by row. In the past, if you wanted to modify a small amount of data in a table or partition, MaxCompute read all data in the table or partition. After the data was modified, you had to execute an INSERT OVERWRITE statement to write all the data into the table or partition. The newly supported UPDATE and DELETE statements significantly reduce the amount of data that needs to be read and written. Furthermore, if you execute statements that include the INSERT, UPDATE, and DELETE operations on a table, you can execute a MERGE INTO statement instead. The MERGE INTO statement requires only one data scan to complete all the preceding operations, which is more efficient. The MERGE INTO statement ensures the atomicity of the multiple operations involved. If an operation fails, the job fails. This prevents the failure to roll back or undo some operations in a MERGE INTO statement. Only transactional tables support UPDATE, DELETE, and MERGE INTO statements. Big data systems ensure job-level atomicity, consistency, isolation, durability (ACID). If multiple jobs that have the same destination table concurrently run, job conflicts may occur. For more information, see the description of ACID for concurrent writes in MaxCompute. UPDATE, DELETE, and MERGE INTO statements are in public preview. If you execute these statements, no fees are generated. However, the jobs and data that are generated by using these statements cannot be guaranteed during the public preview. We recommend that you back up important data before you execute these statements.