DML for (MariaDB/MySQL): Data Update, Delete

Posted by thomashw on Sun, 19 May 2019 09:27:22 +0200

Contents of this article:
1.update statement
2.delete statement
　2.1 Form Delete
　2.2 Multi-table Deletion
3.truncate table

1.update statement

update is used to modify records in a table.

# Form update syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
  [PARTITION (partition_list)]
  SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]

# Multi-table update syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
    [WHERE where_condition]

The following clauses and keyword-related functions are briefly introduced, and they are explained in detail later.

  • low_priority is only valid for storage engines that use table-level locks, such as MyISAM and Arria, which set the delete statement to take precedence over the read operation, delaying update until no process accesses the table.See: (MariaDB/MySQL) Priority of read and write operations for the MyISAM storage engine.
  • ignore ignores errors when updating one row and continues updating other rows.
  • The where clause filters out rows to be updated.If no where clause is given, update updates all rows in the entire table.
  • The order by clause indicates that the filtered data is sorted first, then the rows are updated in order.When updating some rows, using order by can resolve some errors.
  • The limit clause means that a certain number of rows are updated.

For example:

# Form Update
UPDATE table_name SET column1 = value1, column2 = value2 WHERE id=100;

Updates the specified number of rows in order.

update book set bookcount=2 where bookname in ('ss') order by bookid limit 10;

Multi-table update.Note that the following statement updates the data in both tables.

UPDATE BOOK,BOOK2 SET BOOK.bookcount=2 ,BOOK2.bookcount=3 WHERE BOOK.bookid=1 AND BOOK2.bookid=1;

Update a table's data based on another table.Note that the following statement only updates data in one table.

update t,t1 set t1.name='newname' where t1.id=t2.id;
update t set name='newname' where t.id=(select max(id) from t1);

Note that SQL Server supports the update front syntax below, but MySQL/MariaDB does not.

-- Use multi-table joins to subtract 5 points from students with less than 65 points on a software test
UPDATE TScore SET mark = mark - 5 
FROM TScore a JOIN TSubject b ON a.subJectID = b.subJectID
WHERE b.subJectName = 'software test' AND mark < 65

Below are a few special cases about update s that need to be noted.

(1). Consider using the order by clause when there are duplicate key values when updating.

For example, the following table: id is the primary key and duplicates are not allowed.

create or replace table t(id int primary key,sex char(3),name char(20));
insert into t values(1,'nan','longshuai1'),
                      (2,'nan','longshuai2'),
                      (3,'nv','xiaofang1'),
                      (4,'nv','xiaofang2'),
                      (5,'nv','xiaofang3'),
                      (6,'nv','xiaofang4'),
                      (7,'nv','tun\'er'),
                      (8,'nan','longshuai3');

The following statement will fail to update because the primary key id will repeat if the update is successful.

update t set id=id+1 where id>5;
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'

However, after using order by, it will update normally because it will sort first and then update by descending result set.

update t set id=id+1 where id>5 order by id desc;
select * from t where id>5;
+----+------+------------+
| id | sex  | name       |
+----+------+------------+
|  7 | nv   | xiaofang4  |
|  8 | nv   | tun'er     |
|  9 | nan  | longshuai3 |
+----+------+------------+

(2) It is important to note the concurrency of set assignment statements in update.

Multiple assignment statements are evaluated from left to right, unless sql_mode specifies the SIMULTANEOUS_ASSIGNMENT mode, which is supported starting with MariaDB 10.3.5, in which case the UPDATE statement evaluates all assignment statements at the same time.(Note: The update assignment statements for standard SQL are simultaneous)

For example, given the following table:

CREATE OR REPLACE TABLE tx (c1 INT, c2 INT);
INSERT INTO tx VALUES (10,10);

The update below executes correctly, and the value of the c2 field is the same as that of c1 after updating.

UPDATE tx SET c1=c1+1,c2=c1;
SELECT * FROM tx;
+------+------+
| c1   | c2   |
+------+------+
|   11 |   11 |
+------+------+

Set the sql_mode l mode SIMULTANEOUS_ASSIGNMENT and execute the same update statement.

/* Since each assignment statement is evaluated at the same time, the updated c1 will be added 1, and the updated c2 will be equal to the updated c1 */
SET @@sql_mode=CONCAT(@@sql_mode,',SIMULTANEOUS_ASSIGNMENT');
UPDATE tx SET c1=c1+1,c2=c1;
SELECT * FROM tx;
+------+------+
| c1   | c2   |
+------+------+
|   12 |   11 |
+------+------+

(3) Update data with the same source and target.

Before MariDB 10.3.2, executing the following update statement will fail.

update t set id='10' where id=(select max(t.id) from t);  
ERROR 1093 (HY000): Table 't' is specified twice, both as a target for 'UPDATE' and as a separate source for data

However, starting with MariaDB 10.3.2, such update statements are allowed to execute.

2.delete statement

Delete is used to delete records in a table.You can delete form data or multi-table data.

Look at the grammar first:

# Form Delete Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 
    FROM tbl_name [PARTITION (partition_list)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
    [RETURNING select_expr 
      [, select_expr ...]]

# Multi-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]
# Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

The following clauses and keyword-related functions are briefly introduced, and they are explained in detail later.

  • The from clause specifies which table of data to delete, and if it is a multi-table grammar, it may simply provide a referential function, not necessarily delete the data.
  • low_priority is only valid for storage engines that use table-level locks, such as MyISAM and Arria, which set the delete statement to take precedence over read operations, delaying delete until no process accesses the table.See: (MariaDB/MySQL) Priority of read and write operations for the MyISAM storage engine.
  • Quck is a notification storage engine that merges deletion operations. When the storage engine receives this notification, deletion of multiple rows is merged into a batch. Deleting a batch only once it reaches a certain size can improve the efficiency of deleting data.It may not be valid for InnoDB/XtraDB, but it is valid for MyISAM and Arria.
  • ignore ignores errors when deleting one row and continues deleting other rows.
  • The where clause filters out rows to be deleted.If no where clause is given, delete deletes all rows in the entire table.
  • The order by clause indicates that the filtered data is sorted before the rows are deleted in order.
  • The limit clause means that a certain number of rows are deleted.
  • The returning clause is used to return data related to the deleted row.This is a very human feature of MariaDB that not only lets us know which rows have been deleted, it can sometimes be used to restore rows that have been deleted by mistake.MySQL does not support this feature.
  • The using clause is used for multi-table deletion syntax.

The from clause must be used in the delete statement in MySQL/MariaDB.When deleting a form, the table name must be placed in the from clause, while in the multi-table deletion syntax, a multi-table can be placed before the from clause.People accustomed to SQL Server may not be accustomed to it at first, and deletes in SQL Server tend not to write from clause for convenience reasons.

2.1 Form Delete

Give the table below and insert some data.

create or replace table t(id int primary key,sex char(3),name char(20));
insert into t values(1,'nan','longshuai1'),
                      (2,'nan','longshuai2'),
                      (3,'nv','xiaofang1'),
                      (4,'nv','xiaofang2'),
                      (5,'nv','xiaofang3'),
                      (6,'nv','xiaofang4'),
                      (7,'nv','tun\'er'),
                      (8,'nan','longshuai3');

Delete records with sex='nv'and id>6.

delete from t where id>6 and sex='nv';

For delete statements, the order by clause is used primarily in conjunction with the limit clause.

delete from t order by id limit 2;

If you use the returning clause, you can customize which data is returned when rows are deleted.Note that the following statements before MariaDB 10.3.1 will fail.See below.

delete from t where id=(select max(id) from t) returning concat("delete id: ",id) as maxid;
+--------------+
| maxid        |
+--------------+
| delete id: 8 |
+--------------+

Or return the values of all fields deleting rows:

delete from t returning *;
+----+------+-----------+
| id | sex  | name      |
+----+------+-----------+
|  3 | nv   | xiaofang1 |
|  4 | nv   | xiaofang2 |
|  5 | nv   | xiaofang3 |
|  6 | nv   | xiaofang4 |
+----+------+-----------+

Note that in the delete statement below, the same source and target data are deleted.Prior to MariDB 10.3.1, the delete statement could not delete such a record.The error message is as follows:

delete from t where id=(select max(id) from t);
ERROR 1093 (HY000): Table 't' is specified twice, both as a target for 'DELETE' and as a separate source for data

However, deletion of such records is allowed after MariaDB 10.3.1.

2.2 Multi-table Deletion

There are two grammars, one is to place the table reference before the from clause and the other is to use the using clause.They are actually equivalent.

If the syntax below is not clear, consider replacing delete tbl_name with select column_list.The delete process is the same as the select process, except that after filtering the data, one is to further select the filtered result set and the other is to delete the filtered result set.

The following statement deletes the records in the tables t and t1 that satisfy the id equivalence.Notice that the contents of both tables have been deleted.

delete t,t1 from t join t1 on t.id=t1.id;
# Equivalent to
delete from t,t1 using t join t1 on t.id=t1.id;

If you only want to delete the contents of one table but need to reference more than one table, you can refer to the following statement.This statement only deletes the contents of the t table, not the t1 table.

# delete tbl_name1 from tbl_name1 join tbl_name2 ....
delete t from t join t1 on t.id=t1.id;

For example, delete records in table t but not in table t1.

delete t from t left join t1 on t.id=t1.id where t1.id is NULL;

If an alias is used, an alias is required when a delete list references a table name, as is the case with select.

# Correct grammar
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;

# Incorrect syntax
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2 WHERE a1.id=a2.id;
DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2 WHERE a1.id=a2.id;

3.truncate table

truncate table is used to empty a table.truncate table is equivalent to drop table + re-create table operations, so it is a DDL statement, not a DML statement, and therefore requires drop privileges on the table, which is much faster than all the delete tables, especially when the tables are large.

When the re-create table is created, it rebuilds the table based on the table structure in the'.frm'file, so attributes such as indexes are preserved.However, the most recent value of auto_increment is reset because the table is deleted and its auto_increment values are all emptied.

truncate table fails if there are other locks on the table.

truncate table fails if there is a foreign key reference on the table.

If there are triggers on the table, the truncate table will not trigger any triggers.Because MariaDB/MySQL does not support DDL triggers.

 

Back to the Linux series article outline: http://www.cnblogs.com/f-ck-need-u/p/7048359.html
Back to the Outline of the Site Architecture Series: http://www.cnblogs.com/f-ck-need-u/p/7576137.html
Back to the database series article outline: http://www.cnblogs.com/f-ck-need-u/p/7586194.html
For reprint, please indicate the source: http://www.cnblogs.com/f-ck-need-u/p/8912026.html

Note: If you think this article is good, please click on the recommendation at the bottom right corner. Your support will inspire the author to write more enthusiastically. Thank you very much!

Topics: MySQL MariaDB SQL less

Hot Topics

©2024 Programmer Think