It remembers......
mysql> create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY
KEY(id)) ;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)
mysql> update tt set id=10 where v="one";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into tt (v) VALUES("two");
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt;
+----+------+
| id | v |
+----+------+
| 10 | one |
| 11 | two |
+----+------+
2 rows in set (0.00 sec)
mysql> update tt set id=40 where v="two";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tt;
+----+------+
| id | v |
+----+------+
| 10 | one |
| 40 | two |
+----+------+
2 rows in set (0.00 sec)
mysql> update tt set id=30 where v="two";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into tt (v) VALUES("three");
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt;
+----+-------+
| id | v |
+----+-------+
| 10 | one |
| 30 | two |
| 41 | three |
+----+-------+
3 rows in set (0.00 sec)
After doing this fiddling, show create table shows the auto increment value
mysql> show create table tt;
+-------+-------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------
| tt | CREATE TABLE `tt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`v` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------
1 row in set (0.00 sec)
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of Simon Slavin
Sent: Tue 01 November 2016 11:42
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE
On 1 Nov 2016, at 11:14am, Andy Ling <[email protected]> wrote:
> MySQL lets you fiddle.....
I don't have MySQL. To satisfy my curiosity, could you try this:
create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ;
insert into tt (v) VALUES("one");
update tt set id=10 where v="one";
insert into tt (v) VALUES("two");
select * from tt;
You got this far already (though I changed the details a little).
Now add this:
update tt set id=40 where v="two";
select * from tt;
update tt set id=30 where v="two";
insert into tt (v) VALUES("three");
select * from tt;
I'm trying to find out whether it remembers that there once was a value that
was 40, even if the highest value present when you do the insert is 30. My
guess is that it'll assign an id of 41 to row three, but I might be wrong.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---------------------------------------------------------------------------------------
This email has been scanned for email related threats and delivered safely by
Mimecast.
For more information please visit http://www.mimecast.com
---------------------------------------------------------------------------------------
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
