CODEWITHSUNDEEP
sqlsql-servert-sqlinsert-update
JPS
JPS

Reputation: 21

How to update the data based on column which has bit data type in T-SQL

enter image description here

Test Table with 3 columns (FirstName, LastName, Status)

I want to update first names and last names of all rows in a single UPDATE query for any status either it is true or false. How do I write this?

Upvotes: 0

Views: 3889

Answers (2)

Dmitriy Khaykin
Dmitriy Khaykin

Reputation: 5258

The SQL Server bit data type stores 1/0 for true/false values, and will convert the strings 'true' and 'false' to 1 and 0 respectively. Thus you can query based on '0' or '1', or 'False' or 'True'

If you want logic to update all rows with a single update query, and have a different update depending on the value of [Status] column, then you need a case statement:

There are multiple ways to do this, some examples of which can be seen in the example and results below:

-- Let's make a test table that mimicks your schema and put some data in it    
-- We will use a function to re-make the table
Create Function MakeTestTable ()
Returns @Test Table (
    FirstName varchar(50), 
    LastName varchar(50), 
    [Status] bit
)
As Begin
    Insert Into @Test Values 
            ('John', 'Doe', 'false'), 
            ('Jane', 'Dane', 'true'),
            ('Space', 'Cadet', Null)
    Return 
End
Go

-- Our local test table variable for examples:
Declare @Test Table (
    FirstName varchar(50), 
    LastName varchar(50), 
    [Status] bit
)

-- Let's seed the test table
Insert Into @Test Select * From MakeTestTable()

-- Verify initial Values 
Select * From @Test

-- Single update statement depending on [Status] value using Case statement:
Update @Test 
Set FirstName = 
    Case -- Here we use 1/0 for True/False, respectively 
        When [Status] = 1 Then 'FirstName updated while Status was True' 
        When [Status] = 0 Then 'FirstName updated while Status was False'
        Else 'FirstName updated while Status was Null'
    End,
    LastName = 
    Case -- Here we use 'True' and 'False' which SQL converts to 1 and 0, respectively
        When [Status] = 'True' Then 'LastName updated while Status was True' 
        When [Status] = 'False' Then 'LastName updated while Status was False'
        Else 'LastName updated while Status was Null'
    End

-- Verify our updates:
Select * From @Test

-- Reset for next example:
Delete @Test; Insert Into @Test Select * From MakeTestTable()

-- Single update statement based on [Status] value not being null using Case statement:
Update @Test 
Set FirstName = 
    Case -- Here we update if the value of [Status] is either 0 or 1 using In clause
        When [Status] In (0,1) Then 'FirstName updated while Status was True or False' 
        Else 'FirstName updated while Status was neither 0 nor 1'
    End,
    LastName = 
    Case -- Here we base our update on [Status] being null (reverse of the above)
        When [Status] Is Not Null Then 'LastName updated while Status was Not Null' 
        Else 'LastName updated while Status was Null'
    End

-- Verify our updates:
Select * From @Test

-- Reset for next example:
Delete @Test; Insert Into @Test Select * From MakeTestTable()

-- Finally, update all columns based on status not being null using Where clause:
-- Without the CASE statement, the WHERE clause is applied to the entire update,
-- The code is simpler, but you lose the ability to update each column based on a 
-- different condition.
Update @Test 
Set FirstName = 'FirstName updated while Status is not Null',
    LastName = 'LastName updated while Status is not Null'
Where [Status] Is Not Null

-- Verify our updates:
Select * From @Test

-- Clean up
Drop Function MakeTestTable

Query results

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

If Status does not allow null then every row is true or false 

update table set firstname = 'fname';

If Status does allow null then 

update table set firstname = 'fname' where status is not null;

if you have different updates for different values then just use two updates 

update table set firstname = 'fnameT' where status = 'true'; 
update table set firstname = 'fnameF' where status = 'false';

Upvotes: 1

Related Questions