You're essentially emulating autoincrement behavior with the IFNULL(MAX()) + 1 clause, but the manual emulation works on arbitrary columns, unlike the built-in autoincrement. This assumes a table definition with no declared AUTO_INCREMENT column. WHERE `app_id` = 3 AND `test_id` = 1 /* same values as in inserted columns */ IFNULL(MAX(`sr_no`), 0) + 1 /* next sequence number */,įROM `issue_log` /* specify the table name as well */ SELECT FROM the same table, like so: mysql> INSERT INTO `issue_log` (`sr_no`, `app_id`, `test_id`, `issue_name`) SELECT I don't fully understand your increment requirement on the test_id column, but if you want an ~autoincrement sequence that restarts on every unique combination of ( app_id, test_id), you can do an INSERT. avoid triggers/procedures if possible - which was suggested for similar cases on other Questions). I want to achieve this with as much simplicity as possible (i.e. The sr_no col should increment for unique app_id and test_id. I have an Application Table (with app_id as its primary key), each Application has a set of Issues to be resolved, and each Application has multiple number of tests (so the test_id col) Of course, there has to be something wrong with my query, because of which the error thrown is:ĮRROR 1075: Incorrect table definition there can be only one auto I need to implement a Composite Key that will help identify the row as unique using combination of these three (one foreign keys and one auto_increment column, and one other column with non-unique values) I have a table which references two foreign keys, and for each unique combination of these foreign keys, has its own auto_increment column.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |