MariaDB [(none)]> source c:/xampp/subconsultas.sql Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.004 sec) Database changed Query OK, 0 rows affected (0.014 sec) Query OK, 0 rows affected (0.011 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 5 rows affected (0.001 sec) Records: 5 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.016 sec) Query OK, 0 rows affected (0.016 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 7 rows affected (0.001 sec) Records: 7 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) MariaDB [subconsultas]> show tables; +------------------------+ | Tables_in_subconsultas | +------------------------+ | articulo | | copia | | detalle | +------------------------+ 3 rows in set (0.006 sec) MariaDB [subconsultas]> 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.013 sec) MariaDB [subconsultas]> 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.016 sec) MariaDB [subconsultas]> update detalle set valorventa = (select valorunitario + (valorunitario * 0.23) from articulo where articulo.codigo = detall -> e.codigo); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'e.codigo)' at line 2 MariaDB [subconsultas]> update detalle set valorventa = (select valorunitario + (valorunitario * 0.23) from articulo where articulo.codigo = detalle.codigo); Query OK, 7 rows affected (0.003 sec) Rows matched: 7 Changed: 7 Warnings: 0 MariaDB [subconsultas]> 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.002 sec) MariaDB [subconsultas]> 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.001 sec) MariaDB [subconsultas]> update detalle set total = cantidad * valorventa; Query OK, 7 rows affected (0.002 sec) Rows matched: 7 Changed: 7 Warnings: 0 MariaDB [subconsultas]> 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.002 sec) MariaDB [subconsultas]> update articulo set existencia = cantidad - (select sum(cantidad) from detalle where detalle.cantidad = articulo.cantidad); Query OK, 0 rows affected, 5 warnings (0.002 sec) Rows matched: 5 Changed: 0 Warnings: 5 MariaDB [subconsultas]> 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.002 sec) MariaDB [subconsultas]> 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.001 sec) MariaDB [subconsultas]> delete from articulo where codigo = (select codigo from detalle where cantidad between 2 and 5 and detalle.codigo = articul -> .codigo group by articulo.codigo); ERROR 1054 (42S22): Unknown column 'articul.codigo' in 'where clause' MariaDB [subconsultas]> delete from articulo where codigo = (select codigo from detalle where cantidad between 2 and 5 and detalle.codigo = articulo.codigo group by articulo.codigo); Query OK, 4 rows affected (0.003 sec) MariaDB [subconsultas]> select * from articulo; +--------+----------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+----------+----------+---------------+------------+ | 350 | lavadora | 13 | 980000 | 0 | +--------+----------+----------+---------------+------------+ 1 row in set (0.001 sec) MariaDB [subconsultas]> create table copia like detalle; ERROR 1050 (42S01): Table 'copia' already exists MariaDB [subconsultas]> show tables; +------------------------+ | Tables_in_subconsultas | +------------------------+ | articulo | | copia | | detalle | +------------------------+ 3 rows in set (0.005 sec) MariaDB [subconsultas]> describe copia; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | nrofactura | char(10) | NO | | NULL | | | fecha | date | NO | | NULL | | | cantidad | int(11) | NO | | NULL | | | valorventa | int(11) | NO | | NULL | | | total | int(11) | NO | | NULL | | | codigo | char(10) | NO | | NULL | | +------------+----------+------+-----+---------+----------------+ 7 rows in set, 2 warnings (0.062 sec) MariaDB [subconsultas]> 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.001 sec) MariaDB [subconsultas]> 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.002 sec) MariaDB [subconsultas]> insert into copia select * from detalle where month(fecha)=2; ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' MariaDB [subconsultas]> 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.001 sec) MariaDB [subconsultas]> exit