Comparing more than one column & update

**davef** · Sep 26 '07, 01:33 PM

Originally posted by cris1978

Hi,

I'm trying to do this query to update the results in a table, it's compiling and running without problems, but it's not updating the table, could you guys help me?

Many Thanks!

_______________ _______________ _______________ _______________ ____
DECLARE
@COUNT INT
BEGIN

SET @COUNT=(SELECT (Segunda-Sexta) FROM tblMedicao WHERE IDItem = '15')

IF @COUNT > 0
BEGIN
IF (SELECT Segunda FROM tblMedicao)='0'
BEGIN
SET @COUNT = (SELECT (Terça-Sexta) FROM tblMedicao WHERE IDItem = '15')
UPDATE
tblMedicao
SET
[Registros_Atual izados_Sem1] = @COUNT
WHERE IDItem = '15'
END
ELSE
IF (SELECT Terça FROM tblMedicao)='0'
BEGIN
SET @COUNT = (SELECT (Quarta-Sexta) FROM tblMedicao WHERE IDItem = '15')
UPDATE
tblMedicao
SET
[Registros_Atual izados_Sem1] = @COUNT
WHERE IDItem = '15'
END
ELSE
IF (SELECT Quarta FROM tblMedicao)='0'
BEGIN
SET @COUNT = (SELECT (Quinta-Sexta) FROM tblMedicao WHERE IDItem = '15')
UPDATE
tblMedicao
SET
[Registros_Atual izados_Sem1] = @COUNT
WHERE IDItem = '15'
END
ELSE
IF (SELECT Quinta FROM tblMedicao)='0'
BEGIN
UPDATE
tblMedicao
SET
[Registros_Atual izados_Sem1] = Sexta
WHERE IDItem = '15'
END

END
END
_______________ _______________ _______________ _______________ ______

I suggest that for starters you plant PRINT statements in the spots where updates might occur to better see the flow of batch execution:

Code:

DECLARE @COUNT INT
BEGIN
	SET @COUNT=(SELECT (Segunda-Sexta) FROM tblMedicao WHERE IDItem = '15')
	IF @COUNT > 0
	BEGIN
		IF (SELECT Segunda FROM tblMedicao)='0'
		BEGIN
			SET @COUNT = (SELECT (Terça-Sexta) FROM tblMedicao WHERE IDItem = '15')
			PRINT 'SELECT Segunda FROM tblMedicao'
			UPDATE tblMedicao SET [Registros_Atualizados_Sem1] = @COUNT
			WHERE IDItem = '15'
		END
		ELSE
			IF (SELECT Terça FROM tblMedicao)='0'
			BEGIN
				SET @COUNT = (SELECT (Quarta-Sexta) FROM tblMedicao WHERE IDItem = '15')
				PRINT 'SELECT Terça FROM tblMedicao'
				UPDATE tblMedicao 
				SET [Registros_Atualizados_Sem1] = @COUNT
				WHERE IDItem = '15'
			END
			ELSE
				IF (SELECT Quarta FROM tblMedicao)='0'
				BEGIN
					SET @COUNT = (SELECT (Quinta-Sexta) FROM tblMedicao WHERE IDItem = '15')
					PRINT 'SELECT Quarta FROM tblMedicao'
					UPDATE tblMedicao 
					SET [Registros_Atualizados_Sem1] = @COUNT
					WHERE IDItem = '15'
				END
				ELSE
					IF (SELECT Quinta FROM tblMedicao)='0'
					BEGIN
						PRINT 'SELECT Quinta FROM tblMedicao'
						UPDATE tblMedicao 
						SET [Registros_Atualizados_Sem1] = Sexta
						WHERE IDItem = '15'
					END

	END
END

**cris1978** · Sep 28 '07, 01:35 PM

Originally posted by davef

I suggest that for starters you plant PRINT statements in the spots where updates might occur to better see the flow of batch execution:

Code:

DECLARE @COUNT INT
BEGIN
	SET @COUNT=(SELECT (Segunda-Sexta) FROM tblMedicao WHERE IDItem = '15')
	IF @COUNT > 0
	BEGIN
		IF (SELECT Segunda FROM tblMedicao)='0'
		BEGIN
			SET @COUNT = (SELECT (Terça-Sexta) FROM tblMedicao WHERE IDItem = '15')
			PRINT 'SELECT Segunda FROM tblMedicao'
			UPDATE tblMedicao SET [Registros_Atualizados_Sem1] = @COUNT
			WHERE IDItem = '15'
		END
		ELSE
			IF (SELECT Terça FROM tblMedicao)='0'
			BEGIN
				SET @COUNT = (SELECT (Quarta-Sexta) FROM tblMedicao WHERE IDItem = '15')
				PRINT 'SELECT Terça FROM tblMedicao'
				UPDATE tblMedicao 
				SET [Registros_Atualizados_Sem1] = @COUNT
				WHERE IDItem = '15'
			END
			ELSE
				IF (SELECT Quarta FROM tblMedicao)='0'
				BEGIN
					SET @COUNT = (SELECT (Quinta-Sexta) FROM tblMedicao WHERE IDItem = '15')
					PRINT 'SELECT Quarta FROM tblMedicao'
					UPDATE tblMedicao 
					SET [Registros_Atualizados_Sem1] = @COUNT
					WHERE IDItem = '15'
				END
				ELSE
					IF (SELECT Quinta FROM tblMedicao)='0'
					BEGIN
						PRINT 'SELECT Quinta FROM tblMedicao'
						UPDATE tblMedicao 
						SET [Registros_Atualizados_Sem1] = Sexta
						WHERE IDItem = '15'
					END

	END
END

_______________ _______________ _______________ _______________ __

I tried to use your idea, but doesn't work...It's not reporting any error.
Any suggestion?

**davef** · Sep 28 '07, 01:44 PM

Originally posted by cris1978

_______________ _______________ _______________ _______________ __

I tried to use your idea, but doesn't work...It's not reporting any error.
Any suggestion?

Did the flow of control hit any print statements and what got printed?

Comparing more than one column & update

Comparing more than one column & update

Comment

Comment

Comment