Update Data

This document describes how to update data in MatrixOne using SQL statements.

Prerequisites

Single-node deployment of MatrixOne has been completed.

SQL Statements for Updating Data

There are two ways to update data: UPDATE and INSERT ON DUPLICATE KEY UPDATE.

The differences between the two are as follows:

  • UPDATE:

    • The UPDATE statement is used to update existing rows of data directly.
    • You need to specify the target table, columns to be updated, their corresponding new values, and the update conditions.
    • If the update conditions are met, the data of the existing rows will be modified.
    • No changes will be made if the update conditions are not met.

  • INSERT ON DUPLICATE KEY UPDATE:

    • INSERT ON DUPLICATE KEY UPDATE is an extension of the INSERT statement, used to handle duplicate keys when inserting new rows.
    • When there are duplicate keys in the inserted data, i.e., when the values of specific column(s) or combination of columns are the same as existing rows’ key values, an UPDATE operation will be performed instead of inserting a new row.
    • You can specify the data to be inserted and the update operations to be performed when duplicate key conflicts occur in a single statement.
    • The specified update operations will update the corresponding columns for rows with duplicate keys.

Key Differences:

  • The UPDATE statement is used to directly update existing rows, while the INSERT ON DUPLICATE KEY UPDATE statement is used to handle duplicate keys when inserting data.
  • The UPDATE statement requires you to specify the target table, columns to be updated, their corresponding new values, and the update conditions. The INSERT ON DUPLICATE KEY UPDATE statement allows you to specify the data to be inserted and the update operations in a single statement.

UPDATE

  1. UPDATE table_reference
  2.     SET assignment_list
  3.     [WHERE where_condition]
  4.     [ORDER BY ...]
  5.     [LIMIT row_count]

  1. UPDATE table_reference: Specifies the target table for updating data. The table_reference can be a single table or multiple tables joined together.

  2. SET assignment_list: Specifies the columns and values to be updated. The assignment_list lists column names and their corresponding values, separated by commas. Each column name is followed by an equal sign (=) to associate it with the new value to be updated.

  3. [WHERE where_condition] (optional): The WHERE clause is used to specify the conditions for updating data. Only rows that satisfy the specified conditions will be updated. where_condition is a logical expression that can define conditions using various comparisons and logical operators.

  4. [ORDER BY ...] (optional): The ORDER BY clause is used to sort the rows to be updated based on the specified columns. You can use one or more columns and specify ascending (ASC) or descending (DESC) order. The sorting will affect the order of the updated rows.

  5. [LIMIT row_count] (optional): The LIMIT clause limits the number of rows to be updated. It specifies the maximum number of rows (row_count) to be updated. If the LIMIT clause is not specified, all rows that satisfy the WHERE condition will be updated.

During the data update process, specify the target table, the columns and values to be updated, the update conditions, and optionally, the sorting and limiting parameters to perform flexible data updates according to your requirements.

INSERT ON DUPLICATE KEY UPDATE

  1. > INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  2.   [ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...];
  1. INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  2. The INSERT INTO statement inserts new rows into a table.
  3. [db.] (optional) specifies the database name where the table is located. If not provided, the default is the current database.
  4. table is the name of the target table where the data will be inserted.
  5. [(c1, c2, c3)] (optional) specifies the columns to be inserted, enclosed in parentheses and separated by commas. If column names are not specified, it is assumed that all available columns in the table will be inserted.

  6. The VALUES clause specifies the values to be inserted. Each value corresponds to its respective column and is separated by commas and enclosed in parentheses. Multiple rows of data can be inserted, with each row separated by commas.

  7. [ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...]

  8. The ON DUPLICATE KEY UPDATE clause handles duplicate keys when inserting data.

  9. When there are duplicate keys in the inserted data, i.e., when the values of specific column(s) or combination of columns are the same as existing rows’ key values, an UPDATE operation will be performed instead of inserting a new row.
  10. column1, column2, column3 represent the column names to be updated, and value1, value2, value3 represent the corresponding values to be updated.

This syntax allows you to insert one or multiple rows of data into the specified table.

. If a duplicate key situation occurs, i.e., a row with the same key value already exists, an UPDATE operation is executed to update that row’s data.

When using the INSERT INTO statement, provide the corresponding column names and values based on the table structure and requirements. If duplicate keys are encountered, and the ON DUPLICATE KEY UPDATE clause is used, specify the columns to be updated and their corresponding values.

Examples

  • Example 1: UPDATE
  1. -- Create table
  2. CREATE TABLE employees (
  3.   id INT PRIMARY KEY,
  4.   name VARCHAR(50),
  5.   department VARCHAR(50),
  6.   salary DECIMAL(10, 2)
  7. );
  9. -- Insert data
  10. INSERT INTO employees (id, name, department, salary)
  11. VALUES (1, 'John Doe', 'HR', 5000),
  12.        (2, 'Jane Smith', 'Marketing', 6000),
  13.        (3, 'Mike Johnson', 'IT', 7000),
  14.        (4, 'Emily Brown', 'Finance', 8000),
  15.        (5, 'David Wilson', 'HR', 5500);
  17. -- View initial data
  18. mysql> SELECT * FROM employees;
  19. +------+--------------+------------+---------+
  20. | id   | name         | department | salary  |
  21. +------+--------------+------------+---------+
  22. |    1 | John Doe     | HR         | 5000.00 |
  23. |    2 | Jane Smith   | Marketing  | 6000.00 |
  24. |    3 | Mike Johnson | IT         | 7000.00 |
  25. |    4 | Emily Brown  | Finance    | 8000.00 |
  26. |    5 | David Wilson | HR         | 5500.00 |
  27. +------+--------------+------------+---------+
  28. 5 rows in set (0.01 sec)
  30. -- Update data using the UPDATE statement. The salary of the first two employees in the 'HR' department is increased by 10%. The WHERE clause specifies the condition for updating the data; only rows with the department 'HR' will be updated. The ORDER BY clause sorts the rows by the id column in ascending order, and the LIMIT clause limits the update to only two rows.
  31. mysql> UPDATE employees
  32.        SET salary = salary * 1.1
  33.        WHERE department = 'HR'
  34.        ORDER BY id
  35.        LIMIT 2;
  36. Query OK, 2 rows affected (0.02 sec)
  38. -- View updated data
  39. mysql> SELECT * FROM employees;
  40. +------+--------------+------------+---------+
  41. | id   | name         | department | salary  |
  42. +------+--------------+------------+---------+
  43. |    2 | Jane Smith   | Marketing  | 6000.00 |
  44. |    3 | Mike Johnson | IT         | 7000.00 |
  45. |    4 | Emily Brown  | Finance    | 8000.00 |
  46. |    1 | John Doe     | HR         | 5500.00 |
  47. |    5 | David Wilson | HR         | 6050.00 |
  48. +------+--------------+------------+---------+
  49. 5 rows in set (0.00 sec)
  • Example 2: INSERT ... ON DUPLICATE KEY UPDATE
  1. -- Create table
  2. CREATE TABLE students (
  3.   id INT PRIMARY KEY,
  4.   name VARCHAR(50),
  5.   age INT,
  6.   grade VARCHAR(10)
  7. );
  9. -- Insert data
  10. INSERT INTO students (id, name, age, grade)
  11. VALUES (1, 'John Doe', 18, 'A'),
  12.        (2, 'Jane Smith', 17, 'B'),
  13.        (3, 'Mike Johnson', 19, 'A'),
  14.        (4, 'Emily Brown', 18, 'A');
  16. -- View initial data
  17. mysql> SELECT * FROM students;
  18. +------+--------------+------+-------+
  19. | id   | name         | age  | grade |
  20. +------+--------------+------+-------+
  21. |    1 | John Doe     |   18 | A     |
  22. |    2 | Jane Smith   |   17 | B     |
  23. |    3 | Mike Johnson |   19 | A     |
  24. |    4 | Emily Brown  |   18 | A     |
  25. +------+--------------+------+-------+
  26. 4 rows in set (0.01 sec)
  28. -- Update data
  29. mysql> INSERT INTO students (id, name, age, grade)
  30.        VALUES (2, 'Jane Smith', 18, 'A')
  31.        ON DUPLICATE KEY UPDATE age = VALUES(age), grade = VALUES(grade);
  32. Query OK, 1 row affected (0.01 sec)
  34. -- View updated data
  35. mysql> SELECT * FROM students;
  36. +------+--------------+------+-------+
  37. | id   | name         | age  | grade |
  38. +------+--------------+------+-------+
  39. |    1 | John Doe     |   18 | A     |
  40. |    3 | Mike Johnson |   19 | A     |
  41. |    4 | Emily Brown  |   18 | A     |
  42. |    2 | Jane Smith   |   18 | A     |
  43. +------+--------------+------+-------+
  44. 4 rows in set (0.00 sec)

In the above examples, a table named students is first created with four columns: id, name, age, and grade. Then, four rows of student data are inserted using the INSERT INTO statement.

Next, the initial data is viewed using a SELECT statement. Then, an INSERT INTO statement is used to insert a new row of student data where the student with id 2 already exists, causing a duplicate key situation. In this case, the ON DUPLICATE KEY UPDATE clause is used to update that row’s data. The columns to be updated, and their corresponding values are specified using the VALUES function.

Finally, the updated data is viewed again using a SELECT statement, showing that the age and grade of the student with id 2 have been updated.