April 25, 2006 at 7:39 am
Hello All,
First of all sorry for posting the same topic again. Actually previously I posted this quey in wrong place. That's why posting again.
I have a table with the following data
Table Name : Door
----------------------
Door_Id Door_Status
========================
1 0
2 0
3 1
4 1
5 1
6 0
7 1
8 1
9 0
10 1
--
--
--
--
Question:
===============
We have to update the table such a way if Door_id = 1 it can update all the record.
For door_id =1 it can update all the records
For door_id =2 it can update record no: 2,4,6,8,10 the records
For door_id =3 it can update record no: 3,6,9 the records
For door_id =4 it can update record no: 4,8 the records
For door_id =5 it can update record no: 5,10 the records
For door_id =6-10 it can update only one record which is equivalent to corresponding
record no.
so for example if door_id=2 I need to update row no 2,4,6,8 & 10 for door_status.
if door_status is 0 make it to 1 or if it is 1 make it 0.
How to do that in a single update statement??
Need all of your utmost help.
Niladri Kumar Saha
April 25, 2006 at 8:05 am
Something like this?
--data
declare @door table (door_id int, door_status bit)
insert @door
select 1, 0
union all select 2, 0
union all select 3, 1
union all select 4, 1
union all select 5, 1
union all select 6, 0
union all select 7, 1
union all select 8, 1
union all select 9, 0
union all select 10, 1
--input
declare @door_id int
set @door_id = 2 --change this as required
--calculation
update @door set door_status = case when door_status = 0 then 1 when door_status = 1 then 0 else null end where door_id % @door_id = 0
select * from @door
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2006 at 8:20 am
Assuming that the pattern you listed is actually what you want, modulo is going to be your best bet. The modulo function returns the remainder of one integer divided by another integer. If x%y = 0, that means that x is an even multiple of y. Which seems to be the pattern you're after.
CREATE PROC BehindDoorNumber
@varDoor int
AS
UPDATE Door
SET Door_Status = 1
WHERE Door_ID % @varDoor = 0
EDIT: See what happens when I step away from the computer? Ryan beat me to it with the same answer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply