Skip to content

Update data

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

Update data SQL statements

You can update data in two ways: UPDATE and INSERT ON DUPLICATE KEY UPDATE.

The difference between the two is:

  • UPDATE:

  • The UPDATE statement is used to directly update the data of an existing row.

  • You need to specify the table, column and corresponding new value to be updated, as well as the conditions for update.
  • If the update condition is met, the data of the existing row will be modified.
  • If the updated conditions are not met, there will be no modification operations.

INSERT ON DUPLICATE KEY UPDATE:

  • INSERT ON DUPLICATE KEY UPDATE is an extension of the INSERT statement to handle duplicate keys when inserting a new row.
  • When there is a duplicate key in the inserted data, that is, the value of a column or combination column is the same as the key value of the existing row, the UPDATE operation is performed instead of inserting a new row.
  • You can specify the data to be inserted and the update operations to be performed in the event of repeated key conflicts at one time.
  • For rows with repeated keys, the corresponding column will be updated according to the specified update operation.

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 needs to specify the table, column and corresponding new value to be updated, as well as the conditions for update. The INSERT ON DUPLICATE KEY UPDATE statement performs an update operation on rows of repeated keys by specifying the data to be inserted at one time and updating operations.

UPDATE

UPDATE table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

  1. UPDATE table_reference: Specifies the target table to update the data. table_reference can be a single table or a join of multiple tables.

  2. SET assignment_list: Specify the column and value to be updated. assignment_list is a list of column names and 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 meet the specified criteria will be updated. where_condition is a logical expression that can use various comparison operators and logical operators to define conditions.

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

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

During the process of updating data, specify the table to be updated, the columns and values ​​to be updated, the conditions to be updated, and the sorting and restrictions to enable flexible data updates based on needs.

INSERT ON DUPLICATE KEY UPDATE

> INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  [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 is used to insert new rows into the table.
  3. [db.] (optional) Specifies the database name where the table resides. If the database name is not provided, the default is the current database.
  4. table is the name of the target table to which the data is to be inserted.
  5. [(c1, c2, c3)] (optional) Specifies the column to be inserted, enclosed in brackets, and separates the column names with commas. If no column name is specified, it is assumed that all available columns in the table will be inserted.

  6. The VALUES clause specifies the value to be inserted. Each value corresponds to the corresponding column one by one, separated by commas, and placed in brackets. Multiple rows of data can be inserted, each row is separated by a comma.

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

  8. ON DUPLICATE KEY UPDATE clause is used to handle Duplicate Key when inserting data.
  9. When there is a duplicate key in the inserted data, that is, the value of a column or combination column is the same as the key value of the existing row, the UPDATE operation is performed instead of inserting a new row.
  10. column1, column2, column3 etc. represent the column name to be updated, and value1, value2, value3 etc. represent the corresponding value to be updated.

This syntax structure allows one or more rows of data to be inserted into a specified table. If a duplicate key occurs, that is, a row with the same key value already exists, you can choose to perform the UPDATE operation to update the data of that row.

Please note that when using the INSERT INTO statement, the corresponding column names and corresponding values ​​are provided according to the table's structure and requirements. If there is a duplicate key, and the ON DUPLICATE KEY UPDATE clause is used, specifying the column to be updated and the corresponding value.

Example

  • Example 1: UPDATE
-- Create a table
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  Salary DECIMAL(10, 2)
);

-- Insert data
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'John Doe', 'HR', 5000),
       (2, 'Jane Smith', 'Marketing', 6000),
       (3, 'Mike Johnson', 'IT', 7000),
       (4, 'Emily Brown', 'Finance', 8000),
       (5, 'David Wilson', 'HR', 5500);

-- View the initial data
mysql> SELECT * FROM employees;
+------+----------------------------------------------+
| id | name | department | salary |
+------+----------------------------------------------+
| 1 | John Doe | HR | 5000.00 |
| 2 | Jane Smith | Marketing | 6000.00 |
| 3 | Mike Johnson | IT | 7000.00 |
| 4 | Emily Brown | Finance | 8000.00 |
| 5 | David Wilson | HR | 5500.00 |
+------+----------------------------------------------+
5 rows in set (0.01 sec)

-- Updated data, updated the salary of the first two employees of the department for 'HR' using the UPDATE statement, and increased the salary by 10%. The WHERE clause specifies the conditions for updating data, and only rows that meet the department 'HR' will be updated. The ORDER BY clause is sorted in ascending order in the id column, while the LIMIT clause limits only two rows of data to be updated.
mysql> UPDATE employees
       SET salary = salary * 1.1
       WHERE department = 'HR'
       ORDER BY id
       LIMIT 2;
Query OK, 2 rows affected (0.02 sec)

-- View updated data
mysql> SELECT * FROM employees;
+------+----------------------------------------------+
| id | name | department | salary |
+------+----------------------------------------------+
| 2 | Jane Smith | Marketing | 6000.00 |
| 3 | Mike Johnson | IT | 7000.00 |
| 4 | Emily Brown | Finance | 8000.00 |
| 1 | John Doe | HR | 5500.00 |
| 5 | David Wilson | HR | 6050.00 |
+------+----------------------------------------------+
5 rows in set (0.00 sec)
  • Example 2: INSERT ... ON DUPLICATE KEY UPDATE
-- Create a table
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  grade VARCHAR(10)
);

-- Insert data
INSERT INTO students (id, name, age, grade)
VALUES (1, 'John Doe', 18, 'A'),
       (2, 'Jane Smith', 17, 'B'),
       (3, 'Mike Johnson', 19, 'A'),
       (4, 'Emily Brown', 18, 'A');

-- View the initial data
mysql> SELECT * FROM students;
+-------+-----------+-------+
| id | name | age | grade |
+-------+-----------+-------+| 1 | John Doe | 18 | A |
| 2 | Jane Smith | 17 | B |
| 3 | Mike Johnson | 19 | A |
| 4 | Emily Brown | 18 | A |
+-------+-----------+-------+
4 rows in set (0.01 sec)

-- Update data
mysql> INSERT INTO students (id, name, age, grade)
       VALUES (2, 'Jane Smith', 18, 'A')
       ON DUPLICATE KEY UPDATE age = VALUES(age), grade = VALUES(grade);
Query OK, 1 row affected (0.01 sec)

-- View updated data
mysql> SELECT * FROM students;
+-------+-----------+-------+
| id | name | age | grade |
+-------+-----------+-------+
| 1 | John Doe | 18 | A |
| 3 | Mike Johnson | 19 | A |
| 4 | Emily Brown | 18 | A |
| 2 | Jane Smith | 18 | A |
+-------+-----------+-------+
4 rows in set (0.00 sec)

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

Next, use the SELECT statement to view the initial data. Then, a row of student data is inserted using the INSERT INTO statement, where a student with id is 2 already exists, which leads to a duplicate key situation. In this case, use the ON DUPLICATE KEY UPDATE clause to update the data for that row. Through the VALUES function, we specify the column to be updated and the corresponding value.

Finally, use the SELECT statement to view the updated data again, and you can see that the age and grades of students with id of 2 have been updated.