Update CASE WHEN THEN PROBLEM

[elexender]

Hi all,

Im running a update query like this to update multiple rows(about 1000).

Example of query:

UPDATE DATA1 SET Column = CASE WHEN TIME = 0 THEN X
	WHEN TIME = 1 THEN y
        WHEN TIME = 2 THEN Z
        WHEN Time = 3 THEN Q END

The problem is the query sets all other rows to NULL in ms sql database.
I only want the query to update the rows in the WHEN THEN statement and leave the other rows in the column as is.

It seems like it ads a

WHEN Time = 3 THEN Q ELSE NULL END

to the last statement.

Is there a way to avoid this? Or maiby there is a better solution for this problem?

thanks!

Alex

[BitBlt]

There are actually two ways to do this, but one of the solutions may not be applicable in your case.

First example, you could just use this query:

UPDATE DATA1 SET [column] = CASE WHEN TIME = 0 THEN X
	WHEN TIME = 1 THEN y
        WHEN TIME = 2 THEN Z
        WHEN Time = 3 THEN Q 
        else [column] END

This causes every row to be updated. If the value in "TIME" is other than the stated ones, it just updates the value in "[column]" to itself. The drawback to this approach is that it it actually does update EVERY row in the table, whether it needs it or not. Depending on the size of the table, this could get very expensive in terms of resources and time.

Second example, you have your table defined so that there is some sort of selection criterion available to limit the rows that are touched. So, for instance, if your "TIME" reference above is actually a column on the table DATA1 you could use this:

UPDATE DATA1 SET [column] = CASE WHEN TIME = 0 THEN X
	WHEN TIME = 1 THEN y
        WHEN TIME = 2 THEN Z
        WHEN Time = 3 THEN Q 
        END
 from DATA1 where time in (0, 1, 2, 3)

Oh, and sorry about the brackets...I had to use those for testing this, since COLUMN is a SQL reserved word.

Hope this helps! Good luck!

[elexender]

Thank you very much BitBlt just what i needed!