select...select(s)
update...select(s)
delete...select(s)
insert...select(s)

update tabla set campo=valor where condicion;

update tabla set (subconsulta),(subconsulta), compo=valor where subconsultas;




Restaurar la base datos subconsultas 



Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------------+
| Tables_in_subconsultas |
+------------------------+
| articulo               |
| detalle                |
+------------------------+
2 rows in set (0.00 sec)

mysql> select * from articulo;
+--------+------------+----------+---------------+------------+
| codigo | articulo   | cantidad | valorunitario | existencia |
+--------+------------+----------+---------------+------------+
| 150    | nevera     |       25 |        950000 |          0 |
| 200    | televisor  |       11 |       1200000 |          0 |
| 250    | estufa     |       30 |        750000 |          0 |
| 300    | ventilador |       17 |        110000 |          0 |
| 350    | lavadora   |       13 |        980000 |          0 |
+--------+------------+----------+---------------+------------+
5 rows in set (0.00 sec)

mysql> select * from detalle;
+----+------------+------------+----------+------------+-------+--------+
| id | nrofactura | fecha      | cantidad | valorventa | total | codigo |
+----+------------+------------+----------+------------+-------+--------+
|  1 | 1200       | 2010-01-30 |        3 |          0 |     0 | 150    |
|  2 | 1250       | 2010-02-13 |        5 |          0 |     0 | 150    |
|  3 | 1250       | 2010-02-13 |        7 |          0 |     0 | 250    |
|  4 | 1300       | 2010-03-02 |        1 |          0 |     0 | 350    |
|  5 | 1300       | 2010-03-02 |        2 |          0 |     0 | 300    |
|  6 | 1400       | 2010-03-11 |        3 |          0 |     0 | 200    |
|  7 | 1500       | 2010-03-21 |        5 |          0 |     0 | 250    |
+----+------------+------------+----------+------------+-------+--------+
7 rows in set (0.00 sec)

1.   calcular el valorventa de la tabla detalle con un incremento del 23% del valorunitario de la tabla articulo


mysql> update detalle set valorventa = (select valorunitario + (valorunitario * 0.23) from articulo where articulo.codigo = detall
e.codigo);
Query OK, 7 rows affected (0.19 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from detalle;
+----+------------+------------+----------+------------+-------+--------+
| id | nrofactura | fecha      | cantidad | valorventa | total | codigo |
+----+------------+------------+----------+------------+-------+--------+
|  1 | 1200       | 2010-01-30 |        3 |    1168500 |     0 | 150    |
|  2 | 1250       | 2010-02-13 |        5 |    1168500 |     0 | 150    |
|  3 | 1250       | 2010-02-13 |        7 |     922500 |     0 | 250    |
|  4 | 1300       | 2010-03-02 |        1 |    1205400 |     0 | 350    |
|  5 | 1300       | 2010-03-02 |        2 |     135300 |     0 | 300    |
|  6 | 1400       | 2010-03-11 |        3 |    1476000 |     0 | 200    |
|  7 | 1500       | 2010-03-21 |        5 |     922500 |     0 | 250    |
+----+------------+------------+----------+------------+-------+--------+
7 rows in set (0.00 sec)

mysql> select * from articulo;
+--------+------------+----------+---------------+------------+
| codigo | articulo   | cantidad | valorunitario | existencia |
+--------+------------+----------+---------------+------------+
| 150    | nevera     |       25 |        950000 |          0 |
| 200    | televisor  |       11 |       1200000 |          0 |
| 250    | estufa     |       30 |        750000 |          0 |
| 300    | ventilador |       17 |        110000 |          0 |
| 350    | lavadora   |       13 |        980000 |          0 |
+--------+------------+----------+---------------+------------+
5 rows in set (0.00 sec)

2.  calcular el total de la tabla detalle

mysql> update detalle set total = cantidad * valorventa;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from detalle;
+----+------------+------------+----------+------------+---------+--------+
| id | nrofactura | fecha      | cantidad | valorventa | total   | codigo |
+----+------------+------------+----------+------------+---------+--------+
|  1 | 1200       | 2010-01-30 |        3 |    1168500 | 3505500 | 150    |
|  2 | 1250       | 2010-02-13 |        5 |    1168500 | 5842500 | 150    |
|  3 | 1250       | 2010-02-13 |        7 |     922500 | 6457500 | 250    |
|  4 | 1300       | 2010-03-02 |        1 |    1205400 | 1205400 | 350    |
|  5 | 1300       | 2010-03-02 |        2 |     135300 |  270600 | 300    |
|  6 | 1400       | 2010-03-11 |        3 |    1476000 | 4428000 | 200    |
|  7 | 1500       | 2010-03-21 |        5 |     922500 | 4612500 | 250    |
+----+------------+------------+----------+------------+---------+--------+
7 rows in set (0.00 sec)


3.  calcular las existencias segun la tabla detalle de cada articulo

mysql> update articulo set existencia = cantidad - (select sum(cantidad) from detalle where detalle.cantidad = articulo.cantidad);

Query OK, 0 rows affected, 5 warnings (0.17 sec)
Rows matched: 5  Changed: 0  Warnings: 5


mysql> select * from articulo;
+--------+------------+----------+---------------+------------+
| codigo | articulo   | cantidad | valorunitario | existencia |
+--------+------------+----------+---------------+------------+
| 150    | nevera     |       25 |        950000 |         17 |
| 200    | televisor  |       11 |       1200000 |          8 |
| 250    | estufa     |       30 |        750000 |         18 |
| 300    | ventilador |       17 |        110000 |         15 |
| 350    | lavadora   |       13 |        980000 |         12 |
+--------+------------+----------+---------------+------------+
5 rows in set (0.00 sec)

mysql> select * from detalle;
+----+------------+------------+----------+------------+---------+--------+
| id | nrofactura | fecha      | cantidad | valorventa | total   | codigo |
+----+------------+------------+----------+------------+---------+--------+
|  1 | 1200       | 2010-01-30 |        3 |    1168500 | 3505500 | 150    |
|  2 | 1250       | 2010-02-13 |        5 |    1168500 | 5842500 | 150    |
|  3 | 1250       | 2010-02-13 |        7 |     922500 | 6457500 | 250    |
|  4 | 1300       | 2010-03-02 |        1 |    1205400 | 1205400 | 350    |
|  5 | 1300       | 2010-03-02 |        2 |     135300 |  270600 | 300    |
|  6 | 1400       | 2010-03-11 |        3 |    1476000 | 4428000 | 200    |
|  7 | 1500       | 2010-03-21 |        5 |     922500 | 4612500 | 250    |
+----+------------+------------+----------+------------+---------+--------+
7 rows in set (0.02 sec)


4.  eliminar los articulos que hayan vendido entre 3 y 5 articulos en cada venta

mysql> delete from articulo where codigo = (select codigo from detalle where cantidad between 2 and 5 and detalle.codigo = articul
o.codigo group by articulo.codigo);
Query OK, 4 rows affected (0.00 sec)

mysql> select * from articulo;
+--------+----------+----------+---------------+------------+
| codigo | articulo | cantidad | valorunitario | existencia |
+--------+----------+----------+---------------+------------+
| 350    | lavadora |       13 |        980000 |         12 |
+--------+----------+----------+---------------+------------+
1 row in set (0.00 sec)

crear una tabla copia (duplicar la tabla detalle) y llenarla con la informacion de detalle, solo los registros del mes de febrero

mysql> create table copia like detalle;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------------+
| Tables_in_subconsultas |
+------------------------+
| articulo               |
| copia                  |
| detalle                |
+------------------------+
3 rows in set (0.02 sec)

mysql> describe copia;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| nrofactura | char(10) | NO   |     |         |                |
| fecha      | date     | NO   |     |         |                |
| cantidad   | int(11)  | NO   |     |         |                |
| valorventa | int(11)  | NO   |     |         |                |
| total      | int(11)  | NO   |     |         |                |
| codigo     | char(10) | NO   |     |         |                |
+------------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> select * from detalle;
+----+------------+------------+----------+------------+---------+--------+
| id | nrofactura | fecha      | cantidad | valorventa | total   | codigo |
+----+------------+------------+----------+------------+---------+--------+
|  1 | 1200       | 2010-01-30 |        3 |    1168500 | 3505500 | 150    |
|  2 | 1250       | 2010-02-13 |        5 |    1168500 | 5842500 | 150    |
|  3 | 1250       | 2010-02-13 |        7 |     922500 | 6457500 | 250    |
|  4 | 1300       | 2010-03-02 |        1 |    1205400 | 1205400 | 350    |
|  5 | 1300       | 2010-03-02 |        2 |     135300 |  270600 | 300    |
|  6 | 1400       | 2010-03-11 |        3 |    1476000 | 4428000 | 200    |
|  7 | 1500       | 2010-03-21 |        5 |     922500 | 4612500 | 250    |
+----+------------+------------+----------+------------+---------+--------+
7 rows in set (0.00 sec)

mysql> select * from copia;
Empty set (0.00 sec)


mysql> insert into copia select * from detalle where  month(fecha)=2;
Query OK, 2 rows affected (0.19 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from copia;
+----+------------+------------+----------+------------+---------+--------+
| id | nrofactura | fecha      | cantidad | valorventa | total   | codigo |
+----+------------+------------+----------+------------+---------+--------+
|  2 | 1250       | 2010-02-13 |        5 |    1168500 | 5842500 | 150    |
|  3 | 1250       | 2010-02-13 |        7 |     922500 | 6457500 | 250    |
+----+------------+------------+----------+------------+---------+--------+
2 rows in set (0.00 sec)