ON DUPLICATE KEY UPDATE unt_nb = unt_nb + VALUES(unt_nb)īut, as the unt_nb column is unsigned, MySQL returns an error which says that 5 is out of range (here, negative). INSERT INTO gp_unt (unt_id, unt_nb) VALUES (1, -1) INSERT INTO gp_unt (unt_id, unt_nb) VALUES (1, 6)Īnd then, if you want to update the col unt_nb to 5 (in other words, make 6 - 1), you will try this query : ON DUPLICATE KEY" with negative value on unsigned column ! You MUST explicitly provide each column assignment.īe carefull when doing "INSERT INTO. INSERT INTO table (a,b,c,d,e) VALUES (1,2,3,4,5) ON DUPLICATE KEY UPDATE VALUES(b,c,d,e) Īnd it would match the columns you want to update with the values in the INSERT. If you have a lot of columns it would be nice if you could use the following syntax: INSERT INTO table (a,b,c,d,e) VALUES (1,2,3,4,5) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c), d=VALUES(d), e=VALUES(e) This assumes that column 'a' is the unique key. Here is an example of how to update multiple columns using values supplied in the INSERT statement. So keep this in mind when checking for affected rows :-) In the latter case, affected rows will return 2, which is not what one would normally expect. In case 5 already exists, however, an update will be made. INSERT INTO mytable (primaryid, count) VALUES(5, 1) ON DUPLICATE KEY UPDATE count = count + 1 Īssuming primaryid is defined as a primary key, in case the value 5 doesn't exist, it will be inserted and, as expected, the affected rows will be 1. Posted by Dionysis Zindros on Septem7:54amĪccording to, when you use mysql_affected_rows() (for example, if you use PHP, or the equivalent function in your language) to detect the number of affected rows of an insert-on-duplicate, it won't always return what expected. Insert into test2 select * from test1 on duplicate key update a = 'REMOVE-ME' Id int not null auto_increment primary_key, Whats happen with new record that conflicts with an existing one? The first time it'll insert, the rest it'll update. INSERT INTO wordcount (word,count) VALUES ('a_word',1) ON DUPLICATE KEY UPDATE count=count+1 User Comments Posted by Bart Alewijnse on Ap4:39pmĪ slightly simpler example, counting words:ĬREATE TABLE wordcount (word varchar(80) primary key, count integer) The DELAYED option is ignored when you use ON DUPLICATE KEY UPDATE inserts or updates a row, If a table contains an AUTO_INCREMENT column That statement is identical to the following two statements: ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b) Thisįunction is especially useful in multiple-row inserts. In the ON DUPLICATE KEY UPDATE clause refersīe inserted, had no duplicate-key conflict occurred. Try to avoid using an ON DUPLICATE KEY UPDATEĬlause on tables with multiple unique indexes.Ĭontain multiple column assignments, separated by commas. UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1 For example, if columnĪnd contains the value 1, the following twoĪffected-rows value per row is 1 if the row is inserted as a new Row is inserted that would cause a duplicate value in a If you specify ON DUPLICATE KEY UPDATE, and a
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |