SQL: insert all other columns of rows with the same primary key updated.

SQL: insert all other columns of rows with the same primary key updated.

In SQL writing, we often encounter such problems. During each insert operation, you must determine whether the data table already exists. For the same data, all the columns except the primary key are the same. If no data exists, the data is inserted. If yes, the data is updated. In this situation, we do not know whether this column exists, so there is a problem with insertion and update, so some people choose to delete the corresponding id, and then re-insert all, the problem here is that the primary key auto-increment and deletion will produce a huge hole, which is not a good design.

 

In MySQL, I use the following methods:

Insert into case_law (case_id, laws_id) (select 1, 2 from dual where not EXISTS (select case_id, laws_id from case_law where case_id = 1 and laws_id = 2)

The above simple means can be used to check whether the insertion exists.