SQL SERVER : USO DE UPDATE CON CASE
CASE
Ejemplo 01SELECT titulo, precio,
Presupuesto= CASE precio
WHEN precio > 20.00 THEN 'Expensive'
WHEN precio BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN precio < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titulos
Resultado:
Titulo Precio Presupuesto
---------------------- ----------- ---------------
Cooking with Computers 11.95 Moderate
Straight Talk About Co 19.99 Moderate
The Busy Executive's D 19.99 Moderate
You Can Combat Compute 2.99 Inexpensive
Silicon Valley Gastron 19.99 Moderate
The Gourmet Microwave 2.99 Inexpensive
But Is It User Friendl 22.95 Expensive
Secrets of Silicon Val 20.00 Moderate
Net Etiquette (null) Unknown
Ejemplo 02
SELECT producto,
SUM(CASE mes WHEN 1 THEN ventas ELSE NULL END) AS jan,
SUM(CASE mes WHEN 2 THEN ventas ELSE NULL END) AS feb,
SUM(CASE mes WHEN 3 THEN ventas ELSE NULL END) AS mar,
SUM(CASE mes WHEN 4 THEN ventas ELSE NULL END) AS apr,
SUM(CASE mes WHEN 5 THEN ventas ELSE NULL END) AS may,
SUM(CASE mes WHEN 6 THEN ventas ELSE NULL END) AS jun,
SUM(CASE mes WHEN 7 THEN ventas ELSE NULL END) AS jul,
SUM(CASE mes WHEN 8 THEN ventas ELSE NULL END) AS aug,
SUM(CASE mes WHEN 9 THEN ventas ELSE NULL END) AS sep,
SUM(CASE mes WHEN 10 THEN ventas ELSE NULL END) AS oct,
SUM(CASE mes WHEN 11 THEN ventas ELSE NULL END) AS nov,
SUM(CASE mes WHEN 12 THEN ventas ELSE NULL END) AS dec
FROM prodventas
GROUP BY producto
Ejemplo 03
SELECT producto,
SUM(CASE WHEN mth BETWEEN 1 AND 3 THEN ventas
ELSE NULL END) AS q1,
SUM(CASE WHEN mth BETWEEN 4 AND 6 THEN ventas
ELSE NULL END) AS q2,
SUM(CASE WHEN mth BETWEEN 7 AND 9 THEN ventas
ELSE NULL END) AS q3,
SUM(CASE WHEN mth BETWEEN 10 AND 12 THEN ventas
ELSE NULL END) AS q4
FROM prodventas
GROUP BY producto
Ejemplo 04
SELECT au_lname, au_fname, titulo, Category =
CASE
WHEN (SELECT AVG(royaltyper) FROM tituloauthor ta
WHERE t.titulo_id = ta.titulo_id) > 65
THEN 'Very High'
WHEN (SELECT AVG(royaltyper) FROM tituloauthor ta
WHERE t.titulo_id = ta.titulo_id)
BETWEEN 55 and 64
THEN 'High'
WHEN (SELECT AVG(royaltyper) FROM tituloauthor ta
WHERE t.titulo_id = ta.titulo_id)
BETWEEN 41 and 54
THEN 'Moderate'
ELSE 'Low'
END
FROM authors a,
titulos t,
tituloauthor ta
WHERE a.au_id = ta.au_id
AND ta.titulo_id = t.titulo_id
ORDER BY Category, au_lname, au_fname
Ejemplo 05
UPDATE titulos
SET precio =
CASE
WHEN (precio < 5.0 AND ytd_ventas > 999.99)
THEN precio * 1.25
WHEN (precio < 5.0 AND ytd_ventas < 1000.00)
THEN precio * 1.15
WHEN (precio > 4.99 AND ytd_ventas > 999.99)
THEN precio * 1.2
ELSE precio
END
UPDATE
UPDATE emp
SET sal = ( CASE
WHEN e2.sal IS NULL THEN e1.sal
ELSE e2.sal
END )
FROM employee e1 INNER JOIN emp e2
ON e1.empid = e2.empid;
Comentarios
Publicar un comentario