Database Reference
In-Depth Information
INSERT into visitorbook (entryid, entrydate, cookieid, firstname,
familyname, entrytext, score, location)
VALUES (7,'2002-02-20',1,'Paul','Davis','How should I
know?',15,'Dusseldorf')
However, you will notice that we already have a row with the primary key of 7,so this
insert will stop with an error as follows:
Duplicate entry ' 7 ' for key 1
There is a new clause in MySQL version 4.1 that allows the insert to update the duplicate key
if the primary key already exists. To use this we re-write the query as follows:
INSERT into visitorbook (entryid, entrydate, cookieid, firstname,
familyname, entrytext, score, location)
VALUES (7,'2002-02-20',1,'Paul','Davis','How should I
know?',15,'Dusseldorf')
ON DUPLICATE KEY UPDATE entryid = entryid + 1
In this case, the INSERT will run without an error, and the row that used to have a pri-
mary key of 7 will have been replaced with a primary key of 8. This works in this example
because before the insert a row with the primary key of 8 did not exist. If there was such a
row then a similar error would be generated for row 8.
Search WWH ::




Custom Search