Mysql determines whether to insert data based on the conditions, and mysql inserts data based on the conditions.

Mysql determines whether to insert data based on the conditions, and mysql inserts data based on the conditions.

This problem is actually divided into two aspects:

1. Determine whether to insert data based on the table's primary key.

2. Determine whether to insert a table based on its non-primary key.

Suppose there are tables DOC_INFO (Doctor table), combined primary key HOS_ID (hospital code), DEPT_CODE (Department Code), DOC_NO (Doctor code), non-primary key field DOC_NAME Doctor name, etc.

If you determine whether to insert data based on HOS_ID and DOC_NAME, write it as (DULE is a temporary table and does not need to be defined)

Insert into doc_info SELECT 35,12, '20140901', 'cheonan ', 1, '', null,'', '2017-09-21 00:00:00 ', null, '','', null, null

From dual where not exists (SELECT 1 FROM doc_info where hos_id = 35 and doc_name = 'cheonan ')

If you determine whether to insert data based on HOS_ID, DEPT_CODE, and DOC_NO, write

Insert into doc_info VALUES
(3850, '20140901', 'gennan ', 1, '', null,'', '2017-09-21 00:00:00 ',
Null, '','', null, null)
On duplicate key update add_time = '2017-09-21 00:00:00'

 

Differences:

1. Determine whether to insert data based on non-primary keys. It is mainly used to insert data that is not inserted according to the document specification and to temporarily remedy some missing data.

2. Determine whether to insert a primary key to update the latest status of some fields in the table. This is used when fields need to be updated frequently.

Thanks for the technical support:

Https://my.oschina.net/jsan/blog/270161/

Http://www.jb51.net/article/39255.htm