You can’t have MySQL do this for you automatically for InnoDB tables – you would need to use a trigger or procedure, or user another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key. Something like the following should work DELIMITER $$ CREATE TRIGGER xxx BEFORE INSERT ON … Read more
You can achieve this by using DDLEvents. This will allow you to run additional SQL statements just after the CREATE TABLE ran. Look at the examples in the link, but I am guessing your code will look similar to below: from sqlalchemy import event from sqlalchemy import DDL event.listen( Article.__table__, “after_create”, DDL(“ALTER TABLE %(table)s AUTO_INCREMENT … Read more
Use the DEFAULT keyword: $query = “INSERT INTO myTable VALUES (DEFAULT,’Fname’, ‘Lname’, ‘Website’)”; Also, you can specify the columns, (which is better practice): $query = “INSERT INTO myTable (fname, lname, website) VALUES (‘fname’, ‘lname’, ‘website’)”; Reference: http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html
If it is MySql you can try SELECT @n := @n + 1 n, first_name, last_name FROM table1, (SELECT @n := 0) m ORDER BY first_name, last_name SQLFiddle And for SQLServer SELECT row_number() OVER (ORDER BY first_name, last_name) n, first_name, last_name FROM table1 SQLFiddle
Straight after the $wpdb->insert() that does the insert, do this: $lastid = $wpdb->insert_id; More information about how to do things the WordPress way can be found in the WordPress codex. The details above were found here on the wpdb class page
That’s how it’s supposed to work – next_val(‘test_id_seq’) is only called when the system needs a value for this column and you have not provided one. If you provide value no such call is performed and consequently the sequence is not “updated”. You could work around this by manually setting the value of the sequence … Read more
It can be done in a single command. You need to set the IDENTITY property for “auto number”: ALTER TABLE MyTable ADD mytableID int NOT NULL IDENTITY (1,1) PRIMARY KEY More precisely, to set a named table level constraint: ALTER TABLE MyTable ADD MytableID int NOT NULL IDENTITY (1,1), CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyTableID) … Read more
From the answer I got here: You can use: SET [GLOBAL|SESSION] sql_mode=”NO_AUTO_VALUE_ON_ZERO” Which as described here, will prevent MySQL from interpreting an INSERT/UPDATE ID of 0 as being the next sequence ID. Such behaviour will be limited to NULL. It is what I’d consider pretty bad behaviour from the application though. You’ll have to be … Read more
Yes. See here. Section 7.1.9. Change your code to: String sql = “INSERT INTO table (column1, column2) values(?, ?)”; stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); stmt.executeUpdate(); if(returnLastInsertId) { ResultSet rs = stmt.getGeneratedKeys(); rs.next(); auto_id = rs.getInt(1); }
Set the StoreGeneratedPattern attribute to “Identity” in your SSDL for the autoincrement field. It should help.