Control locking priority for table access. These modifiers determine whether read or write operations take precedence when multiple threads access a table.
The InnoDB storage engine uses row-level locking to ensure data integrity. However some storage engines (such as MEMORY, MyISAM, Aria and MERGE) lock the whole table to prevent conflicts. These storage engines use two separate queues to remember pending statements; one is for SELECTs and the other one is for write statements (INSERT, DELETE, UPDATE). By default, the latter has a higher priority.
To give write operations a lower priority, the low_priority_updates server system variable can be set to ON. The option is available on both the global and session levels, and it can be set at startup or via the SET statement.
When too many table locks have been set by write statements, some pending SELECTs are executed. The maximum number of write locks that can be acquired before this happens is determined by the max_write_lock_count server system variable, which is dynamic.
If write statements have a higher priority (default), the priority of individual write statements (INSERT, REPLACE, UPDATE, DELETE) can be changed via the LOW_PRIORITY attribute, and the priority of a SELECT statement can be raised via the HIGH_PRIORITY attribute. Also, supports a LOW_PRIORITY attribute for WRITE locks.
If read statements have a higher priority, the priority of an INSERT can be changed via the HIGH_PRIORITY attribute. However, the priority of other write statements cannot be raised individually.
The use of LOW_PRIORITY or HIGH_PRIORITY for an INSERT prevents from being used.
This page is licensed: CC BY-SA / Gnu FDL
Learn to change and delete data in MariaDB Server. This section covers UPDATE and DELETE SQL statements, enabling you to modify existing records and remove unwanted information efficiently.
Remove specific rows from a table. This statement deletes records that match a specified condition, ensuring data cleanup and maintenance.
Single-table syntax:
Multiple-table syntax:
Or:
Trimming history:
For the single-table syntax, the DELETE statement deletes rows from tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the function. TheWHERE clause, if given, specifies the conditions that identify which rows to delete. With no WHERE clause, all rows are deleted. If the clause is specified, the rows are deleted in the order that is specified. The clause places a limit on the number of rows that can be deleted.
For the multiple-table syntax, DELETE deletes from eachtbl_name the rows that satisfy the conditions. In this case, and > cannot be used. A DELETE can also reference tables which are located in different databases; see for the syntax.
where_condition is an expression that evaluates to true for each row to be deleted. It is specified as described in .
Currently, you cannot delete from a table and select from the same table in a subquery.
You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause. See .
As stated, a DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not need to know the number of deleted rows, is to use TRUNCATE TABLE. However,
within a transaction or if you have a lock on the table,TRUNCATE TABLE cannot be used whereas DELETE can. See , and .
Single-table DELETE statements support aliases. For example:
Single-table DELETE statements do not support aliases.
See for details.
See .
It is possible to return a resultset of the deleted rows for a single table to the client by using the syntax DELETE ... RETURNING select_expr [, select_expr2 ...]]
Any of SQL expression that can be calculated from a single row fields is allowed. Subqueries are allowed. The AS keyword is allowed, so it is possible to use aliases.
The use of aggregate functions is not allowed. RETURNING cannot be used in multi-table DELETEs.
It is possible to delete from a table with the same source and target. For example:
You can use DELETE HISTORY to delete historical information from .
How to use the and clauses:
How to use the RETURNING clause:
The following statement joins two tables: one is only used to satisfy a WHERE condition, but no row is deleted from it; rows from the other table are deleted, instead.
The statement returns:
The statement returns:
This page is licensed: GPLv2, originally from
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name [PARTITION (partition_list)]
[FOR PORTION OF PERIOD FROM expr1 TO expr2]
[AS alias] -- from MariaDB 11.6
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
[RETURNING select_expr
[, select_expr ...]]DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]DELETE HISTORY
FROM tbl_name [PARTITION (partition_list)]
[BEFORE SYSTEM_TIME [TIMESTAMP|TRANSACTION] expression]Returning clause (video)
LOW_PRIORITY
Wait until all SELECT's are done before starting the statement. Used with storage engines that uses table locking (MyISAM, Aria etc). See HIGH_PRIORITY and LOW_PRIORITY clauses for details.
QUICK
Signal the storage engine that it should expect that a lot of rows are deleted. The storage engine can do things to speed up the DELETE like ignoring merging of data blocks until all rows are deleted from the block (instead of when a block is half full). This speeds up things at the expanse of lost space in data blocks. At least MyISAM and Aria support this feature.
IGNORE
Don't stop the query even if a not-critical error occurs (like data overflow). See How IGNORE works for a full description.
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1), (2);
DELETE FROM t1 AS a1 WHERE a1.c1 = 2;DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);DELETE FROM page_hit ORDER BY TIMESTAMP LIMIT 1000000;DELETE FROM t RETURNING f1;
+------+
| f1 |
+------+
| 5 |
| 50 |
| 500 |
+------+DELETE post FROM blog INNER JOIN post WHERE blog.id = post.blog_id;CREATE TABLE t1 (c1 INT, c2 INT);
DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);Query OK, 0 rows affected (0.00 sec)ERROR 1093 (HY000): Table 't1' is specified twice, both as a target for 'DELETE'
AND AS a separate source FORREPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or aUNIQUE index, the old row is deleted before the new row is inserted. If the table has more than one UNIQUE keys, it is possible that the new row conflicts with more than one row. In this case, all conflicting rows will be deleted.
The table name can be specified in the form db_name.tbl_name or, if a default database is selected, in the form tbl_name (see Identifier Qualifiers). This allows to use REPLACE ... SELECT to copy rows between different databases.
Basically it works like this:
The above can be replaced with:
REPLACE is a MariaDB/MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For other MariaDB/MySQL extensions to standard SQL --- that also handle duplicate values --- see IGNORE and INSERT ON DUPLICATE KEY UPDATE.
Note that unless the table has a PRIMARY KEY orUNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.
Values for all columns are taken from the values. See Partition Pruning and Selection for details. Specified in theREPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as 'SET col = col + 1', the reference to the column name on the right hand side is treated asDEFAULT(col), so the assignment is equivalent to'SET col = DEFAULT(col) + 1'.
To use REPLACE, you must have both theINSERT and DELETE privileges for the table.
There are some issues you should be aware of, before using REPLACE:
If there is an AUTO_INCREMENT field, a new value will be generated.
If there are foreign keys, ON DELETE action will be activated by REPLACE.
Triggers on DELETE and INSERT will be activated by REPLACE.
To avoid some of these behaviors, you can use INSERT ... ON DUPLICATE KEY UPDATE.
This statement activates INSERT and DELETE triggers. See Trigger Overview for details.
See Partition Pruning and Selection for details.
REPLACE ... RETURNING returns a resultset of the replaced rows. This returns the listed columns for all the rows that are replaced, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.
Simple REPLACE statement:
Using stored functions in RETURNING:
Subqueries in the statement:
Subqueries in the RETURNING clause that return more than one row or column cannot be used..
Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used, or it can be used in REPLACE...SELECT.
REPLACE ... RETURNING returns a result set of the replaced rows. This returns the listed columns for all the rows that are replaced, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.
Simple REPLACE statement:
Using stored functions in RETURNING:
Subqueries in the statement:
Subqueries in the RETURNING clause that return more than one row or column cannot be used..
Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used, or it can be used in REPLACE...SELECT...RETURNING if the table in the RETURNING clause is not the same as the REPLACE table. SELECT...RETURNING if the table in the RETURNING clause is not the same as the REPLACE table.
INSERT DELAYED for details on the DELAYED clause
This page is licensed: GPLv2, originally from fill_help_tables.sql
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[RETURNING select_expr
[, select_expr ...]]REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[RETURNING select_expr
[, select_expr ...]]REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
SELECT ...
[RETURNING select_expr
[, select_expr ...]]BEGIN;
SELECT 1 FROM t1 WHERE KEY=# FOR UPDATE;
IF FOUND-ROW
DELETE FROM t1 WHERE KEY=# ;
ENDIF
INSERT INTO t1 VALUES (...);
END;REPLACE INTO t1 VALUES (...)REPLACE INTO t2 VALUES (1,'Leopard'),(2,'Dog') RETURNING id2, id2+id2
as Total ,id2|id2, id2&&id2;
+-----+-------+---------+----------+
| id2 | Total | id2|id2 | id2&&id2 |
+-----+-------+---------+----------+
| 1 | 2 | 1 | 1 |
| 2 | 4 | 2 | 1 |
+-----+-------+---------+----------+DELIMITER |
CREATE FUNCTION f(arg INT) RETURNS INT
BEGIN
RETURN (SELECT arg+arg);
END|
DELIMITER ;
PREPARE stmt FROM "REPLACE INTO t2 SET id2=3, animal2='Fox' RETURNING f2(id2),
UPPER(animal2)";
EXECUTE stmt;
+---------+----------------+
| f2(id2) | UPPER(animal2) |
+---------+----------------+
| 6 | FOX |
+---------+----------------+REPLACE INTO t1 SELECT * FROM t2 RETURNING (SELECT id2 FROM t2 WHERE
id2 IN (SELECT id2 FROM t2 WHERE id2=1)) AS new_id;
+--------+
| new_id |
+--------+
| 1 |
| 1 |
| 1 |
| 1 |
+--------+REPLACE INTO t2 VALUES (1,'Leopard'),(2,'Dog') RETURNING id2, id2+id2
as Total ,id2|id2, id2&&id2;
+-----+-------+---------+----------+
| id2 | Total | id2|id2 | id2&&id2 |
+-----+-------+---------+----------+
| 1 | 2 | 1 | 1 |
| 2 | 4 | 2 | 1 |
+-----+-------+---------+----------+DELIMITER |
CREATE FUNCTION f(arg INT) RETURNS INT
BEGIN
RETURN (SELECT arg+arg);
END|
DELIMITER ;
PREPARE stmt FROM "REPLACE INTO t2 SET id2=3, animal2='Fox' RETURNING f2(id2),
UPPER(animal2)";
EXECUTE stmt;
+---------+----------------+
| f2(id2) | UPPER(animal2) |
+---------+----------------+
| 6 | FOX |
+---------+----------------+REPLACE INTO t1 SELECT * FROM t2 RETURNING (SELECT id2 FROM t2 WHERE
id2 IN (SELECT id2 FROM t2 WHERE id2=1)) AS new_id;
+--------+
| new_id |
+--------+
| 1 |
| 1 |
| 1 |
| 1 |
+--------+Replace rows and retrieve the results immediately. This extension returns the values of the replaced or inserted rows in the same operation.
Or:
Or:
REPLACE ... RETURNING returns a result set of the replaced rows. The statement returns the listed columns for all the rows that are replaced, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.
Simple REPLACE statement:
Using stored functions in RETURNING:
Subqueries in the statement:
Subqueries in the RETURNING clause that return more than one row or column cannot be used..
Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used, or it can be used in REPLACE...SELECT...RETURNING if the table in the RETURNING clause is not the same as the REPLACE table.
(video)
This page is licensed: CC BY-SA / Gnu FDL
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[RETURNING select_expr
[, select_expr ...]]REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[RETURNING select_expr
[, select_expr ...]]For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. TheSET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keywordDEFAULT to set a column explicitly to its default value. TheWHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are
updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.
Both clauses can be used with multiple-table updates.
An UPDATE can also reference tables which are located in different databases; see Identifier Qualifiers for the syntax.
where_condition is an expression that evaluates to true for each row to be updated.
table_references and where_condition are as specified as described in SELECT.
For single-table updates, assignments are evaluated in left-to-right order, while for multi-table updates, there is no guarantee of a particular order. If the SIMULTANEOUS_ASSIGNMENT sql_mode is set, UPDATE statements evaluate all assignments simultaneously.
You need the UPDATE privilege only for columns referenced in an UPDATE that are actually updated. You need only the SELECT privilege for any columns that are read but not modified. See GRANT.
The UPDATE statement supports the following modifiers:
If you use the LOW_PRIORITY keyword, execution of the UPDATE is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). See HIGH_PRIORITY and LOW_PRIORITY clauses for details.
If you use the IGNORE keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are
updated to values that would cause data conversion errors are updated to the closest valid values instead.
See Partition Pruning and Selection for details.
UPDATE statements may have the same source and target. For example, given the following table:
Single-table syntax:
Multiple-table syntax:
This page is licensed: GPLv2, originally from fill_help_tables.sql
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
[PARTITION (partition_list)]
[FOR PORTION OF period FROM expr1 TO expr2]
SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
[WHERE where_condition]DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (10,10), (20,20);
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
SELECT * FROM t1;
+------+------+
| c1 | c2 |
+------+------+
| 10 | 10 |
| 21 | 20 |
+------+------+UPDATE table_name SET column1 = value1, column2 = value2 WHERE id=100;UPDATE tab1, tab2 SET tab1.column1 = value1, tab1.column2 = value2 WHERE tab1.id = tab2.id;REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
SELECT ...
[RETURNING select_expr
[, select_expr ...]]REPLACE INTO t2 VALUES (1,'Leopard'),(2,'Dog') RETURNING id2, id2+id2
AS Total ,id2|id2, id2&&id2;
+-----+-------+---------+----------+
| id2 | Total | id2|id2 | id2&&id2 |
+-----+-------+---------+----------+
| 1 | 2 | 1 | 1 |
| 2 | 4 | 2 | 1 |
+-----+-------+---------+----------+DELIMITER |
CREATE FUNCTION f(arg INT) RETURNS INT
BEGIN
RETURN (SELECT arg+arg);
END|
DELIMITER ;
PREPARE stmt FROM "REPLACE INTO t2 SET id2=3, animal2='Fox' RETURNING f2(id2),
UPPER(animal2)";
EXECUTE stmt;
+---------+----------------+
| f2(id2) | UPPER(animal2) |
+---------+----------------+
| 6 | FOX |
+---------+----------------+REPLACE INTO t1 SELECT * FROM t2 RETURNING (SELECT id2 FROM t2 WHERE
id2 IN (SELECT id2 FROM t2 WHERE id2=1)) AS new_id;
+--------+
| new_id |
+--------+
| 1 |
| 1 |
| 1 |
| 1 |
+--------+