Update status in database

Question

In my project I have one task related to the SQL server database.

Employee
======================
id  Name  Active
1   a     0
2   b     0
3   c     1
4   d     1

Here is my table in a grid format and when the admin click on the status update button I want to update the status in one GO. For example here, Id 1 and 2 have 0, and Id 3 and 4 have 1. So, when the admin clicks on the button I want to update the field and make something like this as shown below.

Employee
======================
id  Name  Active
1   a     1
2   b     1
3   c     0
4   d     0

Now Id 1 and 2 becomes 1 and Id 3 and 4 become 0. Please suggest. How to update the field.

use boolean in place of a number in Active attribute refer to this ans https://stackoverflow.com/questions/15183012/update-a-boolean-to-its-opposite-in-sql-without-using-a-select — user55924, Mar 08 '21 at 05:11
Does this answer your question? [Update a boolean to its opposite in SQL without using a SELECT](https://stackoverflow.com/questions/15183012/update-a-boolean-to-its-opposite-in-sql-without-using-a-select) — Will B., Mar 08 '21 at 05:15
As a trainee you will be better of doing some research, taking an SQL tutorial, learning about select, insert, update & delete, than just having someone give you the answer on a plate. — Dale K, Mar 08 '21 at 05:18

Tim Biegeleisen · Accepted Answer · 2021-03-08T08:47:37.593

8

If you just want to toggle the 0/1 values in the Active column, then you may use:

UPDATE Employee
SET Active = 1 - Active;

Another way, using the modulus:

UPDATE Employee
SET Active = 2 % (Active + 1);

edited Mar 08 '21 at 08:47

answered Mar 08 '21 at 05:09

Tim Biegeleisen

502,043
27
286
360

score 2 · Answer 2 · answered Mar 08 '21 at 05:30

2

Just for clarity purpose, using CASE expression.

UPDATE Employee
SET Active = CASE Active WHEN 1 THEN 0 
                         WHEN 0 THEN 1
                         ELSE NULL
                         END

answered Mar 08 '21 at 05:30

Venkataraman R

12,181
2
31
58

Bijay · Answer 3 · 2021-03-08T11:55:35.513

-2

if active = 0 and not active makes opposite of 0 which is 1 and vice versa. Hope it works.

UPDATE Employee SET Active= NOT Active

edited Mar 08 '21 at 11:55

answered Mar 08 '21 at 05:19

Bijay

319
2
7

Please add some explanation to your SQL. – rbr94 Mar 08 '21 at 09:11

Update status in database

3 Answers3