“This is the sixth day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Table record management

The test table

create table test(
    id int PRIMARY key auto_increment,
    uname VARCHAR(20),
    sex TINYINT default 1
)
Copy the code

Data update

Normal operation

Set column_name = xx to assign a value to the specified field. If the value is not specified, the old value will be maintained

UPDATE test
SET uname = 'newe',
 sex = 0
WHERE
 id = 5;
Copy the code

extension

We can also modify the conditions to match 1 or more records that meet the requirements with other conditions, and uniformly override the values as set in the set statement block

Bulk changes

The batch modification here, in contrast to the extended operation type above, updates multiple records at once. But there is a very big difference: the same is 1 SQL, but each record needs to maintain the field value, but not necessarily the same!! For example, I need to set name=’a’ for a record with id=1 and set name=’b’ for a record with id=2.

The most common operation is to break the data that needs to be maintained into a single UPDATE statement. Smaller orders of magnitude can tolerate for loops to maintain individual records, but this is still highly discouraged

There are three recommended batch update schemes:

(1) usingreplace into

Delete the original record and then batch insert, this will have a fatal problem: if we use this scheme update, missing some of the data with fields, the data will be lost!! The data is not in SQL when the second insert operation is performed.

REPLACE INTO test_tbl (id, dr)
VALUES
    (1.'2'),
    (2.'3'),... (x,'y'
);
Copy the code

(2) useduplicate key

Duplicate key can update the original record in place. Compared with REPLACE INTO, the performance of duplicate key is slightly worse. The advantage is that we can update some fields as needed

INSERT INTO test_tbl (id, dr)
VALUES
	(1.'2'),
	(2.'3'),... (x,'y'))ON DUPLICATE KEY 
UPDATE dr = VALUES	(dr);
Copy the code

(3) usingupdate categories

Write it in visible code

update categories 
    set orderid = case id 
        when 1 then 3 
        when 2 then 4 
        when 3 then 5 
    end, 
    title = case id 
        when 1 then 'new title 1'
        when 2 then 'new title 2'
        when 3 then 'new title 3'
    end
where id in (1.2.3)
Copy the code

The method is feasible, but the practical experience is not as good as the above two, the statement is wordy, the principle is to use a similar switch… Case Controls records that match the condition.

It is sufficient to know that there is a method for batch updating