MySQL/Language/Data manipulation

INSERT

The syntax is as follows:

Insert value1 into Column1, value2 into Column2, and value3 into Column3:

 INSERT INTO TableName (Column1, Column2, Column3)
 VALUES (value1, value2, value3)

Insert one record (values are inserted in the order that the columns appear in the database):

 INSERT INTO TableName
 VALUES (value1, value2, value3)

Insert two records:

 INSERT INTO TableName
 VALUES (value1, value2, value3), (value4, value5, value6)

 INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00);
 INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman');

You can also insert records 'selected' from other table.

 INSERT INTO table1(field1, field2)
 SELECT field1, field2
 FROM table2

 INSERT INTO World_Events SELECT * FROM National_Events

Performance tips:

• To insert many rows, consider using LOAD DATA INFILE instead.
• If bulk INSERTs are too slow and they operate on indexed non-empty tables, maybe you should increase the value of bulk_insert_buffer_size.
• Before performing bulk inserts, you may want to disable the keys.
• LOCKing a table also speeds up the INSERT.

UPDATE

The syntax is:

 UPDATE table SET field1 = newvalue1, field2 = newvalue2 WHERE criteria ORDER BY field LIMIT n

Examples are:

 UPDATE owner SET ownerfirstname = 'John'
   WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase');
 
 UPDATE antiques SET price = 500.00 WHERE item = 'Chair';
 
 UPDATE order SET discount=discount * 1.05
 
 UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID
   SET tbl1.col1 = tbl1.col1 + 1
   WHERE tbl2.status='Active'
 
 UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz')
 
 UPDATE products_categories AS pc
   INNER JOIN products AS p ON pc.prod_id = p.id
   SET pc.prod_sequential_id = p.sequential_id
 
 UPDATE table_name SET col_name =
   REPLACE(col_name, 'host.domain.com', 'host2.domain.com')

 UPDATE posts SET deleted=True
   ORDER BY date LIMIT 1

With ORDER BY you can order the rows before updating them, and only update a given number of rows (LIMIT).

It is currently not possible to update a table while performing a subquery on the same table. For example, if I want to reset a password I forgot in SPIP:

mysql> UPDATE spip_auteurs SET pass =
 (SELECT pass FROM spip_auteurs WHERE login='paul') where login='admin';
ERROR 1093 (HY000): You can't specify target table 'spip_auteurs' for update in FROM clause

TODO: describes a work-around that I couldn't make to work with MySQL 4.1. Currently the work-around is not use 2 subqueries, possibly with transactions.

Performance tips

• UPDATEs speed depends of how many indexes are updated.
• If you UPDATE a MyISAM table which uses dynamic format, if you make rows larger they could be split in more than one part. This causes reading overhead. So, if your applications often do this, you may want to regularly run an OPTIMIZE TABLE statement.
• Performing many UPDATEs all together on a LOCKed table is faster than performing them individually.

REPLACE

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

IGNORE

Since MySQL 5.5, "INSERT IGNORE" and "REPLACE IGNORE" allow, when a duplicate key error occurs, to display some warnings and avoid the statement to abort.

Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that would cause duplicate-key violations.

DELETE and TRUNCATE

 DELETE [QUICK] FROM `table1`
 TRUNCATE [TABLE] `table1`

• If you don't use a WHERE clause with DELETE, all records will be deleted.
• It can be very slow in a large table, especially if the table has many indexes.
• If the table has many indexes, you can make the cache larger to try making the DELETE faster (key_buffer_size variable).
• For indexed MyISAM tables, in some cases DELETEs are faster if you specify the QUICK keyword (DELETE QUICK FROM ...). This is only useful for tables where DELETEd index values will be reused.
• TRUNCATE will delete all rows quickly by DROPping and reCREATE-ing the table (not all Storage Engines support this operation).
• TRUNCATE is not transaction-safe nor lock-safe.
• DELETE informs you how many rows have been removed, but TRUNCATE doesn't.
• After DELETing many rows (about 30%), an OPTIMIZE TABLE command should make next statements faster.
• For a InnoDB table with FOREIGN KEYs constraints, TRUNCATE behaves like DELETE.

 DELETE FROM `antiques`
   WHERE item = 'Ottoman'
   ORDER BY `id`
   LIMIT 1

You can order the rows before deleting them, and then delete only a given number of rows.