MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements

Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

How To Omit Columns with Default Values in INSERT Statement?

If you don't want to specify values for columns that have default values, or you want to specify values to columns in an order different than how they are defined, you can provide a column list in the INSERT statement. If a column is omitted in the column, MySQL applies 3 rules:

If default value is defined for the column, that default value will be used.
If no default value is defined for the column and NULL is allowed, NULL will be used.
If no default value is defined for the column and NULL is not allowed, MySQL will provide '' for character type columns, and 0 for numeric columns.

The following tutorial exercise gives you some good examples:

mysql> INSERT INTO fyi_links (url, id) 
  VALUES ('sqa.fyicenter.com', 103);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> INSERT INTO fyi_links (id) VALUES (110);
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> INSERT INTO fyi_links (url) 
   VALUES ('www.fyicenter.com');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT id, url, notes, counts, DATE(created) 
   FROM fyi_links;
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 101 | dev.fyicenter.com | NULL  |      0 | 2006-04-30    |
| 102 | dba.fyicenter.com | NULL  |      0 | 2006-07-01    |
| 103 | sqa.fyicenter.com | NULL  |   NULL | 2006-07-01    |
| 110 |                   | NULL  |   NULL | 2006-07-01    |
|   0 | www.fyicenter.com | NULL  |   NULL | 2006-07-01    |
+-----+-------------------+-------+--------+---------------+
5 rows in set (0.00 sec)

What Happens If Unique Value Constraints Are Violated?

If you are inserting a new record that has values violating a unique constraint, you will get an error. Note that primary key column has a unique value constraint by default. The following tutorial exercise gives you some good examples:

mysql> INSERT INTO fyi_links (url) VALUES ('www.other.com');
ERROR 1062 (23000): Duplicate entry '0' for key 1

mysql> INSERT INTO fyi_links VALUES (101, 
  'sql.fyicenter.com', 
  NULL,
  0,
  '2006-04-30');
ERROR 1062 (23000): Duplicate entry '101' for key 1

How To Insert Multiple Rows with One INSERT Statement?

If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives a good example:

mysql> INSERT INTO fyi_links SELECT id+500, REVERSE(url), 
   notes, counts, created FROM fyi_links;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT id, url, notes, counts, DATE(created) 
   FROM fyi_links;
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 101 | dev.fyicenter.com | NULL  |      0 | 2006-04-30    |
| 102 | dba.fyicenter.com | NULL  |      0 | 2006-08-31    |
| 103 | sqa.fyicenter.com | NULL  |   NULL | 2006-08-31    |
| 110 |                   | NULL  |   NULL | 2006-08-31    |
|   0 | www.fyicenter.com | NULL  |   NULL | 2006-08-31    |
| 601 | moc.retneciyf.ved | NULL  |      0 | 2006-04-30    |
| 602 | moc.retneciyf.abd | NULL  |      0 | 2006-08-31    |
| 603 | moc.retneciyf.aqs | NULL  |   NULL | 2006-08-31    |
| 610 |                   | NULL  |   NULL | 2006-08-31    |
| 500 | moc.retneciyf.www | NULL  |   NULL | 2006-08-31    |
+-----+-------------------+-------+--------+---------------+
10 rows in set (0.00 sec)

(Continued on next part...)

Part: 1 2 3 4 5 6 7

Selected Developer Jobs:

More...