Loop for updating rows with a condition

Question

I have 2 bit columns in a table active and suspended The active column already contains values but the suspended column still null

How can I fill the suspended field with values that is opposite of active?

I was stuck in this code..

 Declare @suspended bit;

 --some conditions

 Update Users_mock
 Set Suspended = @Suspended;

http://stackoverflow.com/questions/173717/sql-server-copying-column-within-table and use 'case' to update the col — Vinayak Pahalwan, Dec 19 '12 at 07:06

score 2 · Answer 1 · answered Dec 19 '12 at 06:58

2

A simple case statement should work:

Update userM
Set suspend = case active when 0 then 1 else 0 end

answered Dec 19 '12 at 06:58

dani herrera

score 1 · Answer 2 · edited May 23 '17 at 12:04

1

It says, **Bits in SQL Server are always stored as 1 or 0 in a bitmap. **

You may use a where, if-else or case to update the column.

edited May 23 '17 at 12:04

Community

answered Dec 19 '12 at 06:59

bonCodigo

score 1 · Answer 3 · answered Dec 19 '12 at 07:00

1

select abs(CONVERT(int,@suspended)-1)

answered Dec 19 '12 at 07:00

Mahdi Tahsildari

score 1 · Answer 4 · answered Dec 19 '12 at 07:03

1

Try this:

update Users_mock
set Suspended = case when active=1 THEN 0
else 1
end

answered Dec 19 '12 at 07:03

4b0

score 0 · Accepted Answer · answered Dec 19 '12 at 06:59

0

Please try:

Update Users_mock
Set Suspended = 1-Active;

answered Dec 19 '12 at 06:59

TechDo

5 Answers5