Sobre update

Question

Hola compañeros! Le consulto para ver si me pudiesen dar una mano:

Tengo esta tabla de ejemplo:

CREATE TABLE [dbo].[PRUEBA](
[R] [int] NOT NULL,
[A] [int] NULL,
[B] [int] NULL,
 CONSTRAINT [PK_PRUEBA] PRIMARY KEY CLUSTERED 
(
[R] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

con los siguientes datos:

INSERT INTO [dbo].[PRUEBA]([R], [A], [B])
SELECT 1, 70, 70 UNION ALL
SELECT 2, 67, 67 UNION ALL
SELECT 3, 69, 67 UNION ALL
SELECT 4, 68, 67 UNION ALL
SELECT 5, 67, 67 UNION ALL
SELECT 6, 66, 66

y quiero hacer una actualización con:

DECLARE @v INT 

UPDATE T SET
@v=(SELECT B FROM PRUEBA WHERE R=T.R-1),
B=CASE WHEN ((A-@v)>0 AND (A-@v)<5) THEN @v ELSE A END
FROM PRUEBA AS T WHERE R>1

pero cuando b esta en null necesito ejecutar al menos 3 veces para que el resultado sea el esperado:

Resultado 1ra vez:

R A B
1 70 70
2 67 67
3 69 69
4 68 68
5 67 67
6 66 66

2da vez

R A B
1 70 70
2 67 67
3 69 67
4 68 68
5 67 67
6 66 66

3ra vez

R A B
1 70 70
2 67 67
3 69 67
4 68 67
5 67 67
6 66 66

Entiendo que la subconsulta esta tomando el valor antiguo y no el actualizado, por eso al final (de varios recalculos) el calculo es el esperado. Habría otra forma de hacer la actualización para obtener el resultado?

Saludos y gracias

Carlos

---

Carlos

Answer 1

Cual version de SQL Server usas?

Apartir de la version 2012 contamos con las funciones de offset LAG/LEAD que pudieran ayudar en este problema.

with C1 as (
select R, A, B, LAG(B) OVER(order by R) as prv_B
from T
)
update C1
set B = case when (A - prv_B) between 1 and 4 then prv_B else A end;

Si usas una version anterior entonces tendras que usar un self join (union consigo mismo).

with C1 as (
select A.R, A.A, A.B, B.B as prv_B
from T as A inner join T as B on B.R = A.R - 1
)
update C1
set B = case when (A - prv_B) between 1 and 4 then prv_B else A end;

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 2

Hola AMB! Muchas gracias por tu tiempo en responder!

En un principio sera para 2008.

Estoy tratando de entender lo que me pusistes xq no lo use nunca. 

Trate de copiarlo y ejecutar para ver el resultado y no me actualiza segun lo pretendido.

Tratate de darle vueltas para ver que logro.

Saludos

---

Carlos

Answer 3

CHAR72,

¿Puedes comentar los problemas puntuales que tienes?

Answer 4

No se si será lo mas "lindo" y "bonito", pero esto anda!

declare @r int
DECLARE @v INT 
DECLARE crs CURSOR FOR
SELECT R FROM Prueba WHERE R>1
OPEN crs
FETCH NEXT FROM crs
INTO @R
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @v=B FROM PRUEBA WHERE R=@R-1
UPDATE PRUEBA SET
B=CASE WHEN ((A-@v)>0 AND (A-@v)<5) THEN @v ELSE A END
FROM PRUEBA WHERE R=@r

FETCH NEXT FROM crs
INTO @r
END
CLOSE crs
DEALLOCATE crs

resultado:

R A B
1 70 70
2 67 67
3 69 67
4 68 67
5 67 67
6 66 66

---

Carlos

Answer 5

CHAR72,

¿Puedes comentar los problemas puntuales que tienes?

Hola Williams! Gracias por tu interes! Pero trate de explicarlo en mi post.

Mi problema puntual es que quiero que el update actualice un campo de un registro y en el proximo registro poder obtener ese resultado y asi sucesivamente. Por eso probe con un cursor y con eso funcionaria.

O tu me preguntas sobre lo que comentó AMB?

Saludos

---

Carlos

Answer 6

CHAR72,

Es bueno saber que lograste obtener los resultados esperados pero temo que no bajo la forma correcta, veo que haces uso de cursores y en tanto tengas otros medios de obtener resultados es bueno mirarlo sólo de reojo.

Puedes realizar lo siguiente (SELF JOIN):

UPDATE	t1
SET
	t1.B = CASE WHEN ((t1.A - t2.B) > 0 AND (t1.A - t2.B) < 5) THEN t2.B ELSE t1.A END
FROM	
	PRUEBA t1
	INNER JOIN PRUEBA t2 ON (t1.R - 1 = t2.R)

---

Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

Answer 7

CHAR72,

Es bueno saber que lograste obtener los resultados esperados pero temo que no bajo la forma correcta, veo que haces uso de cursores y en tanto tengas otros medios de obtener resultados es bueno mirarlo sólo de reojo.

Puedes realizar lo siguiente (SELF JOIN):

UPDATE	t1
SET
	t1.B = CASE WHEN ((t1.A - t2.B) > 0 AND (t1.A - t2.B) < 5) THEN t2.B ELSE t1.A END
FROM	
	PRUEBA t1
	INNER JOIN PRUEBA t2 ON (t1.R - 1 = t2.R)

---

Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

Williams: te dire que lo que pusistes es similar a lo que yo plantee en un primer momento, ya que necesitas ejecutarlas 3 veces para realmente obtener el resultado esperado.

Si lo pruebas (puse la creacion de tablas y datos) veras lo que digo. (o a vos te funciona y a mi no?)

Hasta el momento lo unico que funcionaria seria la version con cursores.

Saludos

PD: si NADIE RECOMIENDA el uso de cursores para que estan?

---

Carlos

Answer 8

No noto diferencia en los resultados de ambas soluciones. Seria util usar una data de prueba mas representativa y saber los resultados esperados.

-- set oriented
DECLARE @PRUEBA table (
[R] [int] NOT NULL PRIMARY KEY,
[A] [int] NULL,
[B] [int] NULL
)

INSERT INTO @PRUEBA ([R], [A], [B])
SELECT 1, 70, 70 UNION ALL
SELECT 2, 67, 67 UNION ALL
SELECT 3, 69, 67 UNION ALL
SELECT 4, 68, 67 UNION ALL
SELECT 5, 67, 67 UNION ALL
SELECT 6, 66, 66;

SELECT * FROM @PRUEBA ORDER BY R;

WITH C1 AS (
SELECT A.R, A.A, A.B, B.B AS prv_B
FROM @PRUEBA AS A INNER JOIN @PRUEBA AS B ON B.R = A.R - 1
)
UPDATE C1
SET B = CASE WHEN (A - prv_B) BETWEEN 1 AND 4 THEN prv_B ELSE A END;

SELECT * FROM @PRUEBA ORDER BY R;
GO
-- cursor
DECLARE @PRUEBA table (
[R] [int] NOT NULL PRIMARY KEY,
[A] [int] NULL,
[B] [int] NULL
)

INSERT INTO @PRUEBA ([R], [A], [B])
SELECT 1, 70, 70 UNION ALL
SELECT 2, 67, 67 UNION ALL
SELECT 3, 69, 67 UNION ALL
SELECT 4, 68, 67 UNION ALL
SELECT 5, 67, 67 UNION ALL
SELECT 6, 66, 66;

declare @r int
DECLARE @v INT 
DECLARE crs CURSOR FOR
SELECT R FROM @Prueba WHERE R>1

OPEN crs;

FETCH NEXT FROM crs
INTO @R;

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @v=B 
FROM @PRUEBA 
WHERE R=@R-1;

UPDATE @PRUEBA 
SET
B=CASE WHEN ((A-@v)>0 AND (A-@v)<5) THEN @v ELSE A END
FROM @PRUEBA 
WHERE R=@r

FETCH NEXT FROM crs
INTO @r
END

CLOSE crs;
DEALLOCATE crs;

SELECT * FROM @PRUEBA ORDER BY R;
GO

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 9

CHAR72,

Lo que hace el cursor que has implementado es iterar por cada fila de la tabla y actualizar según el valor de la fila anterior y es lo que hace el código sql que te he proporcionado. En caso no haya entendido el problema te agradeceré expliques con algo de detalle el resultado que esperas dado que los datos de prueba y los resultados que muestras son los mismos.

/*PD: si NADIE RECOMIENDA el uso de cursores para que estan?*/

Los lenguajes de programación son herramientas que nos apoyan a cubrir un objetivo, y dentro de cada lenguaje hay mecanismos que son mas óptimos que otros, si bien es cierto los cursores son "satanizados" habrán circunstancias en las que usarlos sea la única opción y entiendo que eso era muy frecuente en versiones pasadas pero hoy en día es bastante probable encontrar otras formas de realizar lo mismo pero de manera más óptima, la recomendación que te hago es que en tanto tengas mejores formas de realizar una tarea mira de reojo a los cursores.

---

Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

Answer 10

SQL Server esta optimizado para trabajar con conjunto de filas en vez de una fila a la vez.

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 11

En un principio gracias nuevamente AMB y Williams por su tiempo!

Disculpen mi "estupidez" pero la muestra inicial es la que esta mal y quizas por eso ustedes no ven lo que yo veo!

Como habia mencionado "cuando los datos estan en null para B" excepto para R=1, entonce la muestra inicial seria.

INSERT INTO [dbo].[PRUEBA]([R], [A], [B])
SELECT 1, 70, 70 UNION ALL
SELECT 2, 67, null UNION ALL
SELECT 3, 69, null UNION ALL
SELECT 4, 68, null UNION ALL
SELECT 5, 67, null UNION ALL
SELECT 6, 66, null

CON ESA MUESTRA

pero cuando b esta en null necesito ejecutar al menos 3 veces para que el resultado sea el esperado:

Resultado 1ra vez:

R A  B
1 70 70
2 67 67
3 69 69
4 68 68
5 67 67
6 66 66

2da vez

R A  B
1 70 70
2 67 67
3 69 67
4 68 68
5 67 67
6 66 66

3ra vez

R A  B
1 70 70
2 67 67
3 69 67
4 68 67
5 67 67
6 66 66

Ahora si quizas puedan reproducir mi problema! Por favor me avisan, mi equipo de prueba corre:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

Saludos

Carlos

---

Carlos

Answer 12

Entonces no es solo el valor de la fila anterior sino tambien el valor que se arrastra en caso de cumplir la condicion.

En este caso hacer el proceso fila a fila seria una opcion, ya sea usando un cursor o bucle, al menos que alguien encuentre una solucion orientado a conjuntos.

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 13

Williams: esperaba que quizas tengas otra propuesta!

Saludos

Carlos

---

Carlos

Answer 14

CHAR72,

Lo siento, había perdido seguimiento a tu caso. Tengo algo en mente pero déjame probarlo en un momento -es hora de almorzar-, a penas tenga una respuesta (no mas de 2 horas) te dejo mis conclusiones.

Answer 15

bueno bueno... pero gracias compañero que no es urgente !

Ya que te molestes en ayudar (al igual que AMB) es muy meritorio.

Asi que almuerza tranquilo y puedo esperar mucho mas de dos horas!

Saludos

---

Carlos

Answer 16

CHAR72,

Intenté calcular los datos de manera recursiva pero no pude obtener los resultados esperados. Es posible que se trate de un caso en que los cursores sean de ayuda ante la ausencia de otras formas.

En caso resignemos a su uso, es bueno mejorar su rendimiento "ajustando" opciones de configuración, cuida que la configuración no contradiga las operaciones que deseas realizar, mayor información en: DECLARE CURSOR (Transact-SQL)

DECLARE crs CURSOR 
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT R FROM Prueba WHERE R > 1
<...>

---

Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.