28
Feb 2011
A simple SQL quiz
Today at work a colleague came up with a really interesting question. Let’s consider this :
We have a table that has two columns Id, IsValid.
Id is our primary key, while IsValid is a not null bit column.
Can we flip the values of IsValid column in one query?
For example if we have:
| Id | IsValid |
| 1 | 0 |
| 2 | 1 |
| 3 | 0 |
with a single update script to become
| Id | IsValid |
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
Is it possible?
John on said
UPDATE Table
$1SET IsValid = CASE IsValid WHEN 0 THEN 1 ELSE 0 END
djsolid on said
That seems alright, but there is one more alternative that I can think of...
Corcaigh on said
Would this work?
$1
$1UPDATE Table SET IsValid = NOT IsValid
djsolid on said
@Corcaigh I'm afraid it won't
Judah on said
If you are coding against SQL Server, you should be able to use the Bitwise NOT operator (~)
$1
$1http://msdn.microsoft.com/en-us/library/ms173468.aspx
$1
$1Which would make the statement:
$1
$1Update Table
$1SET IsValid = ~IsValid
$1
$1
razvan on said
Also, you could XOR the column with itself.
Phil Bolduc on said
UPDATE Table SET IsValid = ABS(IsValid-1)
webdiyer on said
Update Table SET IsValid = ~IsValid
$1
$1
mahesh on said
update Table set [IsValid]= [IsValid] ^ 1
Will on said
@Phil... better yet... SET IsValue = 1-IsValid
Edwin on said
@Will has the best answer!
Adam on said
Is it just me or is this the easiest quiz I've ever seen? Surely a first year Uni student could solve this one?
ghada houssein on said
update table
$1set isvalid=0
$1where isvalid =1
$1and
$1set isvalid=1
$1where isvalid=0