Esta es la novena parte de una serie de artículos que muestran los conceptos básicos de SQL. En este artículo, echamos un vistazo a algunas de las variaciones de la declaración UPDATE.
- Preparar
- COMMIT y ROLLBACK
- UPDATE Básico
- UPDATE vía View
- Sub-Consultas Escalar
- 0 Rows Updated
Preparar
Puede realizar todas estas consultas en línea de forma gratuita utilizando SQL Fiddle.
Los ejemplos de este artículo requieren que estén presentes las siguientes tablas.
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job VARCHAR2(9),
manager_id NUMBER(4),
hiredate DATE,
salary NUMBER(7,2),
commission NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Estas tablas son una variante de las tablas EMP y DEPT del esquema SCOTT. Verá muchos ejemplos de Oracle en Internet utilizando las tablas del esquema de SCOTT. Puede encontrar las definiciones de la tabla original en el script «$ORACLE_HOME/rdbms/admin/utlsampl.sql».
COMMIT y ROLLBACK
Todos los cambios en el lenguaje de manipulación de datos (DML) se realizan como parte de una transacción. No son permanentes hasta que se confirman mediante la declaración COMMIT. Una vez comprometido, la única forma de revertir un cambio es emitir una nueva declaración DML para alterar los datos. Se pueden agrupar varios extractos para formar una sola transacción.
Los comandos del lenguaje de definición de datos (DDL) realizan una confirmación implícita, que también confirma todos los cambios de DML pendientes en la sesión actual.
Si decide que no desea mantener algunos cambios sin confirmar, puede deshacerse de ellos usando la instrucción ROLLBACK. Muchos de los ejemplos de este artículo emitirán declaraciones ROLLBACK después de la prueba, para revertir los datos a su estado original.
Algunas herramientas y lenguajes de programación se comprometen automáticamente de forma predeterminada, por lo que emiten automáticamente una declaración COMMIT después de cada declaración DML que procesan. No dejes que esto te engañe haciéndote pensar que es un comportamiento predeterminado. No lo es.
UPDATE Básico
La instrucción UPDATE se usa para alterar los valores de columna en una fila existente. Sin una cláusula WHERE, todas las filas de la tabla se actualizan mediante una única declaración.
La siguiente consulta enumera todos los valores de SALARIO en la tabla EMPLOYEES.
SELECT e.salary
FROM employees e
ORDER BY e.salary;
SALARY
----------
800
950
1100
1250
1250
1300
1500
1600
2450
2850
2975
3000
3000
5000
14 rows selected.
SQL>
Actualicémoslos todos agregando 1 a ellos.
UPDATE employees
SET salary = salary + 1;
14 rows updated.
SQL>
SELECT e.salary
FROM employees e
ORDER BY e.salary;
SALARY
----------
801
951
1101
1251
1251
1301
1501
1601
2451
2851
2976
3001
3001
5001
14 rows selected.
SQL>
La cláusula WHERE se utiliza para limitar el alcance de la declaración. Es posible que deseemos actualizar una pequeña cantidad de filas, o incluso una sola fila mediante el uso de un filtro en las columnas de clave principal de la tabla.
UPDATE employees
SET salary = salary - 1
WHERE salary >= 2000;
6 rows updated.
SQL>SELECT e.salary
FROM employees e
ORDER BY e.salary;
SALARY
----------
801
951
1101
1251
1251
1301
1501
1601
2450
2850
2975
3000
3000
5000
14 rows selected.
-- Revertir todos los cambios.
SQL> ROLLBACK;
Se pueden modificar varios valores de columna en una sola instrucción UPDATE.
UPDATE employees
SET salary = 9999,
commission = 1000,
manager_id = 7566
WHERE employee_id = 7369;
1 row updated.
SQL> ROLLBACK;
UPDATE vía View
Es posible actualizar la tabla base asociada con una vista. Hay algunas restricciones asociadas con esto, pero están un poco fuera del alcance de un artículo de nivel principiante. En el siguiente ejemplo, creamos una vista simple en la tabla EMPLOYEES y luego la actualizamos a través de ella.
CREATE OR REPLACE VIEW employees_v AS SELECT * FROM employees; UPDATE employees_v SET salary = 1000 WHERE employee_id = 7369; 1 row updated. SQL> ROLLBACK;
No lo verá con mucha frecuencia, pero también puede actualizar a través de vistas en línea. Esto se puede usar para controlar el número de filas actualizadas, en lugar de usar un filtro en la cláusula WHERE de la propia instrucción UPDATE.
UPDATE (SELECT employee_id, salary
FROM employees
WHERE department_id = 20)
SET salary = 4000;
5 rows updated.
SQL> ROLLBACK;
Sub-Consulta Escalar
El valor actualizado puede provenir de una subconsulta escalar. La siguiente ACTUALIZACIÓN establece el SALARIO del empleado 7369 en el del empleado mejor pagado.
UPDATE employees SET salary = (SELECT MAX(salary) FROM employees) WHERE employee_id = 7369; 1 row updated. SQL> ROLLBACK;
0 Rows Updated
Una actualización de cero filas es una actualización válida y no genera un error. Esto puede resultar bastante confuso para los principiantes.
UPDATE employees SET salary = 10000 WHERE employee_id = 9999; 0 rows updated. SQL>
Como resultado, no puede probar si no se actualizan las filas utilizando la excepción NO_DATA_FOUND en PL/SQL, ya que no se genera.
SET SERVEROUTPUT ON
BEGIN
UPDATE employees
SET salary = 10000
WHERE employee_id = 9999;
DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised');
END;
/
NO_DATA_FOUND Not Raised
PL/SQL procedure successfully completed.
SQL>
En su lugar, debe probar manualmente el número de filas actualizadas mediante SQL%ROWCOUNT.
SET SERVEROUTPUT ON
BEGIN
UPDATE employees
SET salary = 10000
WHERE employee_id = 9999;
IF SQL%ROWCOUNT = 0 THEN
-- Manually raise the NO_DATA_FOUND exception.
RAISE NO_DATA_FOUND;
END IF;
DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised');
END;
/
NO_DATA_FOUND Raised
PL/SQL procedure successfully completed.
SQL>
Pingback: Introducción a SQL - DBandTech.com