A quick question

I have an update statement, in which one of the columns I would only like to update if a condtion is true. How do i do this in my update statement? I am tring the following but doesnt seem to work. Is there a way to only update a column, if a condition(value in input parameter-sp) is true. Please let me know, I am eagerly waiting for some tips. This upd statement is inside a sproc.

UPDATE tblname SET

[NSLDSFlag] = @.NSLDSFlag,

[PinID] = @.PinID,

[PnoteSequenceNumber] = @.PnoteSequenceNumber,

IF (@.ApplicationStatus = 100) THEN [ProcessReturnCode] = NULL,

[ProofOfEmploy] = @.ProofOfEmploy,

[ProofOfEnroll] = @.ProofOfEnroll,

[ProofOfID] = @.ProofOfID

Try the example below.

Chris

UPDATE tblname SET

[NSLDSFlag] = @.NSLDSFlag,

[PinID] = @.PinID,

[PnoteSequenceNumber] = @.PnoteSequenceNumber,

[ProcessReturnCode] = CASE WHEN (@.ApplicationStatus = 100) THEN NULL ELSE [ProcessReturnCode] END,

[ProofOfEmploy] = @.ProofOfEmploy,

[ProofOfEnroll] = @.ProofOfEnroll,

[ProofOfID] = @.ProofOfID

|||It compiles fine, but when I try to run it, it doesnt set the ProcessReturnCode to NULL, if the condition is true. Any idea ?|||Is it not possible to do this using IF Condition, seems very easy, but apparently having a tough time figuring this out. Any help would be appreciated.|||

Try this variation on Chris' suggestion:

UPDATE tblname
SET
[NSLDSFlag] = @.NSLDSFlag,
[PinID] = @.PinID,
[PnoteSequenceNumber] = @.PnoteSequenceNumber,
[ProcessReturnCode] = CASE @.ApplicationStatus
WHEN 100 THEN NULL
ELSE [ProcessReturnCode]
END,
[ProofOfEmploy] = @.ProofOfEmploy,
[ProofOfEnroll] = @.ProofOfEnroll,
[ProofOfID] = @.ProofOfID

Of course, this would only reliably work if @.ApplicationStatus is an integer -and ProcessReturnCode allows NULL values.

|||

The test example below shows that the concept works absolutely fine - maybe there's a problem elsewhere in the code that you're working with?

Chris

DECLARE @.TestTable TABLE (ID INT, String VARCHAR(10) NULL)

INSERT INTO @.TestTable(ID, String)

SELECT 1, 'TestValue'

SELECT ID, String AS [OriginalValue]

FROM @.TestTable

--1 TestValue

DECLARE @.TestValue INT

SET @.TestValue = 0

UPDATE @.TestTable

SET String = CASE WHEN @.TestValue = 100 THEN NULL ELSE String END

SELECT ID, String AS [FailedCondition]

FROM @.TestTable

--1 TestValue

SET @.TestValue = 100

UPDATE @.TestTable

SET String = CASE WHEN @.TestValue = 100 THEN NULL ELSE String END

SELECT ID, String AS [PassedCondition]

FROM @.TestTable

--1 NULL

|||

You can't use IF in an UPDATE statement. The CASE example above should work fine and is well-suited to your application, however if you decide you want to use IF then you could use the code below.

Again, as Arnie pointed out, @.ApplicationStatus should be an INT datatype, and the ProcessReturnCode column must be able to accept NULLs.

Chris

IF @.ApplicationStatus = 100

UPDATE tblname SET

[NSLDSFlag] = @.NSLDSFlag,

[PinID] = @.PinID,

[PnoteSequenceNumber] = @.PnoteSequenceNumber,

[ProcessReturnCode] = NULL,

[ProofOfEmploy] = @.ProofOfEmploy,

[ProofOfEnroll] = @.ProofOfEnroll,

[ProofOfID] = @.ProofOfID

ELSE

UPDATE tblname SET

[NSLDSFlag] = @.NSLDSFlag,

[PinID] = @.PinID,

[PnoteSequenceNumber] = @.PnoteSequenceNumber,

[ProofOfEmploy] = @.ProofOfEmploy,

[ProofOfEnroll] = @.ProofOfEnroll,

[ProofOfID] = @.ProofOfID