May 15, 2010 at 12:02 am
I have a table named attendance
UserID int
PPT bit
Interview bit
GD bit
WrittenTest bit
There are already records in the table
like
USERID PPT Interview GD WrittenTest
1 1 0 1 0
2 0 0 1 null
i need to fire update query to update only those records where value is null or 0
forexample
Update tablename set PPT=0,Interview=1,GD=1,writtentest=1 WHERE UserID=1
In this query it should not update PPT (new value 0) which is already marked as 1
Please suggest some short way instead of Putting UP IF ELSE Condtions for every bit column
May 15, 2010 at 12:13 am
Gaurav, this might help you!
Update tablename
SET
PPT = CASE WHEN ISNULL( NULLIF(PPT,0), 1) = 1 THEN 1 ELSE PPT END
,Interview = CASE ISNULL( NULLIF(PPT,0), 1) = 1 THEN 1 ELSE PPT END
,GD = CASE ISNULL( NULLIF(PPT,0), 1) = 1 THEN 1 ELSE PPT END
,writtentest = CASE ISNULL( NULLIF(PPT,0), 1) = 1 THEN 1 ELSE PPT END
WHERE UserID=1
The code does the following
1. NULLIF substitutes NULL for columns that have 0
2. ISNULL , when finds the column as NULL, substitutes it with 1
Hope this what you wanted! IF not, please post us stil clear requirements wiht some visual representation of desired result!
We will help you out!
Cheers!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply