MySQL replace into statement Usage Details, mysqlreplace

MySQL replace into statement Usage Details, mysqlreplace

When inserting data into a table, you may encounter the following situations:

1. First, determine whether the data exists;
2. If it does not exist, insert it;
3. If yes, it is updated.
 
In SQL Server, you can write as follows:
Copy codeThe Code is as follows:
If not exists (select 1 from table where id = 1) insert into table (id, update_time) values (1, getdate () else update table set update_time = getdate () where id = 1

In MySQL, you can also select to determine whether the statement exists. If yes, update or insert
But there is a simpler method in MySQL, using the replace into keyword
Copy codeThe Code is as follows: replace into table (id, update_time) values (1, now ());
Or
Copy codeThe Code is as follows:
Replace into table (id, update_time) select 1, now ();

Replace into is similar to insert. The difference is that replace into first tries to insert data into the table.

1. If this row of data is found in the table (determined based on the primary key or unique index), delete the row of data and insert new data.
2. Otherwise, insert new data directly.

Note that the table to which data is inserted must have a primary key or a unique index! Otherwise, replace into inserts data directly, which leads to duplicate data in the table.
 
In MySQL, replace into can be written in three ways:
Copy codeThe Code is as follows:
1. replace into table (col,...) values (...)
2. replace into table (col,...) select...
3. replace into table set col = value ,...

The first two forms use more. The "into" keyword can be omitted, but it is better to add "into" to make it more intuitive.
In addition, for columns without values, MySQL automatically assigns default values to these columns.
 
Unfortunately, replace does not support certain update features, so it cannot be used directly as update:

Common update statement: update table set col = col + 1 where id = 1;
Replace into does not support this syntax: replace into table set col = col + 1, id = 1;

1. First, determine whether the data exists. (No problem)
2. If it does not exist, insert it. (No problem)
3. If a field value exists, add or subtract a certain number based on the original value, for example, add one. (Not supported)

How to Use replace in mysql?

Set simtime = replace (time, '-', '');

Mysql's built-in functions are global functions without the concept of objects. Therefore, you cannot use a syntax similar to set simtime = time. replace.

Help me write a mysql statement

MySQL insert when the record does not exist and update when the record exists
There are basically three solutions on the Internet.
First:
Example 1: insert multiple records
Suppose there is a clients table with the primary key of client_id, you can use the following statement:
Insert into clients
(Client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
Where clients. client_id = suppliers. supplier_id );
Example 1: insert a single record
Insert into clients
(Client_id, client_name, client_type)
SELECT 10345, 'ibm ', 'advertising'
FROM dual
WHERE not exists (select * from clients
Where clients. client_id = 10345 );
Using dual as the table name allows you to directly keep up with the values of the insert field after the select statement, even if these values do not exist in the current table.
Second:
Use of on duplicate key update in INSERT
If you specify on duplicate key update and insert a row causes DUPLICATE values in a UNIQUE index or primary key, the old row is updated. For example, if column a is defined as UNIQUE and contains a value of 1, the following two statements have the same effect: mysql> insert into table (a, B, c) VALUES, 3)
-> On duplicate key update c = c + 1;
Mysql> UPDATE table SET c = c + 1 WHERE a = 1;
If a row is inserted as a new record, the value of the affected row is 1. If the original record is updated, the value of the affected row is 2.
NOTE: If Column B is also a unique column, INSERT is equivalent to this UPDATE statement:
Mysql> UPDATE table SET c = c + 1 WHERE a = 1 OR B = 2 LIMIT 1;
If a = 1 OR B = 2 matches multiple rows, only one row is updated. Generally, you should avoid using the on duplicate key clause for tables with multiple unique keywords.
You can use the VALUES (col_name) function in the UPDATE clause to reference the column VALUES from the INSERT section of the INSERT... UPDATE statement. In other words, if no duplicate keyword conflict occurs, VALUES (col_name) in the UPDATE clause can reference the value of col_name to be inserted. This function is particularly applicable to multiple insert statements. The VALUES () function only makes sense in the INSERT... UPDATE statement. Otherwise, NULL is returned.
Example:
Mysql> insert into table (a, B, c) VALUES (1, 2, 3), (4, 5, 6)
-> On duplicate key update c = VALUES (a) + VALUES (B );
This statement serves the same purpose as the following two statements:
Mysql> insert into table (a, B, c) VALUES (1, 2 ...... the remaining full text>