MySQL #1093错误的解决 encountered when using MySQL merge into

February 11, 2018 15:34:23

When doing PHP development, write an SQL statement. That is, if there is a corresponding record in the table, the update is performed and the insert does not exist.

Remember when you did SQL Server, there is a merge into statement, you can select the action you want to perform by query criteria, whether it is a plug-in or an update. But MySQL doesn't seem to have this feature. It is distinguished by whether the primary key is present or not.

So it is customary to write the following statement:

INSERT  intoReadbook (Readid,readpage,readcount,bookid,userid,readtime)VALUES( (SELECTReadid fromReadbookwhereuserid=1  andBookID=1  andReadpage=5),5,1,1,1, now ()) onDuplicateKey UPDATEReadcount=Readcount+ 1, Readtime=Now ()

The result indicates an error, as follows:

Static Analysis: 11 errors were found during analysis. Unrecognized keyword. (Near "Key"At position185) unexpected token. (Near "="At position209) unexpected token. (near "readcount" at position About) unexpected token. (Near "+"At position -) unexpected token. (Near "1"At position222) unexpected token. (near "," at position223) unexpected token. (near "readtime" at position224) unexpected token. (Near "="At position232) Unrecognized keyword. (near ' now ' at position233) unexpected token. (Near "(" at position236) unexpected token. (near ")" at position237) SQL query: DocumentINSERT  intoReadbook (Readid,readpage,readcount,bookid,userid,readtime)VALUES( (SELECTReadid fromReadbookwhereUserid=1  andBookID=1  andReadpage=5) ,5,1,1,1, now ()) onDuplicateKey UPDATEReadcount=Readcount+ 1, Readtime=Now () MySQL return: Document #1093 -You can't specify target table'Readbook'For update in from clause

Error code

Then change the statement:

INSERT  intoReadbook (Readid,readpage,readcount,bookid,userid,readtime)VALUES( (SELECT *  from(SELECTReadid fromReadbookwhereUserid=1  andBookID=1  andReadpage=5) asa),5,1,1,1, now ()) onDuplicateKey UPDATEReadcount=Readcount+ 1, Readtime=Now ()

Prompt for insert success.

The reason for this success is that the 1093 error is: But when you modify a table, the subquery cannot be the same table, the solution: the query and then set a layer, turned into the original table of the grandson query can be

In fact, in this process, because I am not familiar with MySQL, I also encountered a lot of other problems.

Like what

(Select* from (selectfromwhere userid=1  and BookID=1 and Readpage=5 as a

You must have as a because you must give the table an individual name, but I did not require it when I used SQL Server before.

And, yes, that's what I wrote about it.

INSERT  intoReadbook (Readid,readpage,readcount,bookid,userid,readtime)VALUES( (SELECT  Case  whenA.readid>0  ThenA.readidELSE NULL END  from(SELECTReadid fromReadbookwhereuserid=1  andBookID=1  andReadpage=3) asa),3,1,1,1, now ()) onDuplicateKey UPDATEReadcount=Readcount+ 1, Readtime=Now ()

Then gradually try to find this case wehn is not necessary, it seems that MySQL can automatically find the results of the value, the default value instead.

MySQL #1093错误的解决 encountered when using MySQL merge into