ON DUPLICATE KEY UPDATE is taken care of in INSERT query. Javascript code to insert into brand table : `redemption_instructions` varchar(2048) DEFAULT NULL,ĬONSTRAINT `brand_item_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`brand_id`) `is_whole_amount_value_required` varchar(255) DEFAULT NULL, `currency_code` varchar(2048) DEFAULT NULL, `reward_name` varchar(2048) DEFAULT NULL, ![]() ) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1 `terms_and_conditions_instructions` varchar(2048) DEFAULT NULL, `display_instructions` varchar(2048) DEFAULT NULL, `disclaimer_instructions` varchar(2048) DEFAULT NULL, `always_show_disclaimer` varchar(2048) DEFAULT NULL, `last_updated_date` datetime DEFAULT NULL, `short_description` varchar(2048) DEFAULT NULL, `description` varchar(2048) DEFAULT NULL, `brand_id` int(11) NOT NULL AUTO_INCREMENT, Let I have 2 tables in database: (1) brand (2) brand_item. I have multiple rows to insert/update at a time. I got stuck and confused about how to proceed. I thought of using: INSERT INTO beautiful (name, age) Again if 'Samia' is not present, the row should be inserted. 'name' above, should be checked and if such a 'name' already exists, the corresponding whole row should be updated otherwise inserted.įor instance, in the example below, if 'Katrina' is already present in the database, the whole row, irrespective of the number of fields, should be updated. The problem is when I execute this query, I want to check whether a UNIQUE key (which is not the PRIMARY KEY), e.g. so I used something like: $sql = "INSERT INTO beautiful (name, age) Personally i like <= the most in such a case if you ever rearrange the statement.I have a SQL query where I want to insert multiple rows in single query. This works as expected with using = since fielda is already updated to its new value before reaching the if clause of fieldb. In this case fieldb would never be updated because of the I've tried to solve problem like yours & I want to suggest to test from simple aspect.įollow these steps: Learn from simple solution. ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`) Someone else suggested before you can reduce some typing by doing: INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3) However as I said, if you actually use that primary key in other tables, REPLACE INTO probably won't be acceptable to you, as it deletes the old row that clashed via the unique key. That should work - last_insert_id() should have the correct value so long as your primary key is auto-incrementing. I'd like to use the work around to get the ID to! You can't reference the values in the old rows so you can't do an equivalent of INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4) So long as you don't have a primary key on the table that would be fine, but if you do, then if any other table references that primary key It deletes the old row that clashes with the new row and then inserts the new row. However it does not work quite the same as 'ON DUPLICATE UPDATE' There is a MySQL specific extension to SQL that may be what you want - REPLACE INTO
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |