Update with LAST_INSERT_ID : LAST_INSERT_ID « Function « SQL / MySQL






  1. SQL / MySQL
  2. Function
  3. LAST_INSERT_ID

Update with LAST_INSERT_ID

      
mysql>
mysql> CREATE TABLE booksales
    -> (
    ->     title VARCHAR(60) NOT NULL, # book title
    ->     copies INT UNSIGNED NOT NULL, # number of copies sold
    ->     PRIMARY KEY (title)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO booksales (title) VALUES
    -> ('database'),
    -> ('xml'),
    -> ('Java'),
    -> ('SQL'),
    -> ('MySQL');
Query OK, 5 rows affected, 1 warning (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM booksales;
+----------+--------+
| title    | copies |
+----------+--------+
| database |      0 |
| xml      |      0 |
| Java     |      0 |
| SQL      |      0 |
| MySQL    |      0 |
+----------+--------+
5 rows in set (0.00 sec)

mysql>
mysql> UPDATE booksales SET copies = LAST_INSERT_ID(copies+1)
    -> WHERE title = 'Bulldozer';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>
mysql> drop table booksales;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Save the result from LAST_INSERT_ID( ) after creating a new record in a table that has an AUTO_INCREMENT column to a variable
2.Each time a conference registration form is received, enter the attendee information into the attendee table.
3.Using LAST_INSERT_ID() in insert statement
4.To see what the new seminar records look like, use the LAST_INSERT_ID() value to retrieve them:
5.Use the new value to retrieve the entire record, without even knowing what the id is