• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1089
  • Last Modified:

If Statement Using Select Update Set?

I'm setting up flip-flop click event that needs to check or uncheck an SQL 'bit' column, however it's not working.

[code]
If (Select col_Num From table1 Where col_Num = '101')
Begin
Update table1 SET col_Bit = 'True'
End
Else
Begin
Update table1 SET col_Bit = 'False'
End
0
kvnsdr
Asked:
kvnsdr
  • 3
  • 2
  • 2
  • +3
1 Solution
 
Ted BouskillSenior Software DeveloperCommented:
If EXISTS(Select col_Num From table1 Where col_Num = '101')
Begin
    Update table1 SET col_Bit = 'True'
End
Else
Begin
    Update table1 SET col_Bit = 'False'
End
0
 
Scott PletcherSenior DBACommented:
Update table1
SET col_Bit = CASE WHEN col_Num = '101' THEN 1 ELSE 0 END

[SQL bit-type columns use 0/1, not 'true'/'false' (nor 0/-1).]
0
 
kvnsdrAuthor Commented:
I don't know what to think because my table clearly has True or False in the Bit type column.

Thanks I'll try your advice and get back to you....
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kvnsdrAuthor Commented:
Problem:

Both examples set 'every row' in the bit column to True, the back to False when I run the code again.

The row with matching '101' data is set as well as every row in the table...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
are u using SQL server 6.5 or 7.0, I found the similar problems on these versions
0
 
kenhaleyCommented:
update table1
   set col_Bit = case when col_Num = '101' then 1 else 0 end

(1) You're right... bits show as "True" or "False" in the grid display, but they're stored as 1 or 0, and those are the values you must use to update a bit column.
(2) The above statement updates the col_Bit to True (1) if the same row has a col_Num of 101, otherwise it sets col_Bit to False (0).
0
 
kenhaleyCommented:
I just noticed... this is the same as Scott Pletcher's solution.  Use 1 and 0 (not "true" and "false") and I think you'll see that it works just fine.
0
 
kvnsdrAuthor Commented:

It still makes changes on all the other rows in the table.

I just want to flip-flop (toggle) one row.
0
 
Ted BouskillSenior Software DeveloperCommented:
You need to give us a predicate for the where clause on the update statement to restrict it to one row.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess the SQL you want is this:

UPDATE table1
SET col_Bit = CASE WHEN col_Bit = 0 THEN 1 ELSE 0 END
WHERE col_Num = '101'

speaking MS SQL Server with bit column.
if you are using MS Access, the syntax will be a bit different:

UPDATE table1
SET col_Bit = IIF( col_Bit , false, true )
WHERE col_Num = '101'
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now