Record the operation of updating Excel data to database table once

• Background:
  Because it is a production environment, the operator is afraid to update other data, so it can only update according to the Excel data provided by him. Therefore, the following records are available

1、 There are many ways to import Excel data into MySQL database

• Writing Python scripts
• Write shell script
• Generate SQL directly with Excel and execute it in Navicat
  Because I am not familiar with Python and shell, I don’t want to spend so much effort in learning, so I choose to use Excel to generate SQL directly

2、 Steps of importing data with Excel method

1. Create temporary table_ tmp

Method 1:Import and create tables directly using Navicat

All the way to next, you need to enter the name of the new table

Then select the table field, primary key, and import

Law IICreate database tables manually

--Create table
create  table change_tmp(id varchar(50) primary key, balance decimal(20,4));

2. Generate SQL spliced with Excel

Generating data values with Excel

Copy to nodepad

–Generate batch insert statements with excel as long as the contents of brackets are generated, such as:

=CONCATENATE("('",A2,"',",B2,"),")  

You can generate bracket contents (‘dd’, 23.2) with A2 and B2 as data sources. Cells need to be identified with double quotation marks and commas, such as “B2,”

--Insert the last generated SQL of the table
insert into change_tmp values  ('dd',23.2), ('d2d',1.2);

3. Data execution insert

insert into table_tmp values  
('dd',23.2),
('d2d',1.2);

4. Perform batch update

update dest a, change_tmp b set a.balance = b.balance where a.id=b.id;