Do PHP development, to write an SQL statement. That is, if there is a corresponding record in the table, the update will be executed, and if there is no record, the insert will be executed

Remember that when doing SQL server before, there was a merge into statement. You can select the operation to be performed, plug-in or update according to the query conditions. But MySQL doesn’t seem to have this function. It is distinguished by whether the primary key exists or not

So I habitually wrote the following sentence:

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT  readid From readbook where
userid=1 and bookid=1 and readpage=5),5,1,1,1,now()) ON duplicate key UPDATE 

readcount=readcount + 1,readtime=now()

The results show that there are errors as follows:

Static analysis.

11 errors were found during the analysis.

Unrecognized keyword. (near "key" at position 185)
Unexpected token. (near "=" at position 209)
Unexpected token. (near "readcount" at position 210)
Unexpected token. (near "+" at position 220)
Unexpected token. (near "1" at position 222)
Unexpected token. (near "," at position 223)
Unexpected token. (near "readtime" at position 224)
Unexpected token. (near "=" at position 232)
Unrecognized keyword. (near "now" at position 233)
Unexpected token. (near "(" at position 236)
Unexpected token. (near ")" at position 237)
SQL Inquiry: Documentation

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) ,5,1,1,1,now()) ON duplicate key UPDATE readcount=readcount + 1,readtime=now()

MySQL Back to: Documentation

#1093 - You can't specify target table 'readbook' for update in FROM clause

error code

So change the statement:

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT * from (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) as a ),5,1,1,1,now()) ON duplicate key UPDATE readcount=readcount + 1,readtime=now()

It indicates that the insertion is successful

The reason for the success is that the 1093 error is: when modifying a table, the subquery cannot be the same table. The solution is to set the subquery to another level and change it into the grandson query of the original table

In fact, in this process, because I am not familiar with MySQL, I encountered many other problems

For example

(SELECT * from (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) as a

There must be as a, because the table must be given an alias, but I didn’t have this requirement when I used SQL server before

And that’s what I wrote

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT 

CASE WHEN a.readid>0 THEN a.readid ELSE NULL END from(SELECT readid From readbook where 

userid=1 and bookid=1 and readpage=3) as a) ,3,1,1,1,now()) ON duplicate key UPDATE 

readcount=readcount + 1,readtime=now()

Later, I gradually tried to find out that this case wehn is unnecessary. It seems that MySQL can automatically replace the value that cannot be found with the default value