![]() INSERT INTO v8totals (tid,tyy,tmm,finances) Here's how i keep my totals uptodate when the prepays change. I have a table of totals 'v8totals' with PrimaryKey=(tid,tyy,tmm) and a table of records 'bbprepay'. ![]() Now, SELECT LAST_INSERT_ID() will return the correct ID. INSERT INTO test (a,b) VALUES ('1','2') ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID),Dummy = NOT dummy So if you have a table with auto_increment column ID and unique key a,b and a smallint dummy column for instance, the query might look like this: I'm not sure whether this should be regarded as a bug or not, but it does make the construct less useful.Ī way to make things work is to use a dummy column, if there is an existing record with 3 in column c then LAST_INSERT_ID() still won't return the AUTO_INCREMENT id afterwards. This does not work if nothing changes, presumably because MySQL doesn't run the dummy update at all then. Regarding the trick for making LAST_INSERT_ID() well defined for updates: UPDATE table SET c=c+1 WHERE a=1 AND b=2 ![]() So if there is a UNIQUE(a,b) constraint on the table in the example, then the INSERT is equivalent to this UPDATE statement: It is not clearly stated in the documentation above, but if there is a single multiple-column unique index on the table, then the update uses (seems to use) all columns (of the unique index) in the update query. It's wrong of course.įor the moment, you have to let the column signed. 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 id=LAST_INSERT_ID(id), c=3 ![]() ON DUPLICATE KEY UPDATE inserts a row, the 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. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |