March 30, 2011 at 1:48 pm
-- I need to assign Put_it_Here alternating between 1 or 2 (maybe 3 ) perferably using an update statement
---What_I_need and Put_it_Here values should match
Declare @TheTable Table
(Tab_id int,
EmailAddress nvarchar(20),
Type_Here nvarchar(2),
What_I_need int,
Put_it_here int
)
Insert into @TheTable (Tab_id,EmailAddress,Type_Here,What_I_Need,Put_it_Here)
Select 1,'Mickey@Mouse.com','M',1,0
union all
Select 2,'Minny@Mouse.com','M',2,0
union all
Select 3,'Little@Mouse.com','M',1,0
union all
Select 4,'Big@Mouse.com','M',2,0
union all
Select 5,'Fat@Mouse.com','M',1,0
union all
Select 6,'Little@Mouse.com','M',2,0
Union all
Select 7,'Donald@Duck.com','D',1,0
union all
Select 8,'Daisy@Duck.com','D',2,0
union all
Select 9,'Huey@Duck.com','D',1,0
union all
Select 10,'Duey@Duck.com','D',2,0
union all
Select 11,'Loui@Duck.com','D',1,0
union all
Select 12,'Rich@Duck.com','D',2,0
union all
Select 15,'Ms@Duck.com','D',1,0
union all
Select 13,'Goofy@Dog.com','Z',0,0
union all
Select 14,'Pluto@Dog.com','Z',0,0
Select * From @TheTable
where Type_Here = 'M'
and What_I_Need = 1
Select * From @TheTable
where Type_Here = 'M'
and What_I_Need = 2
Select * From @TheTable
where Type_Here = 'D'
and What_I_Need = 1
Select * From @TheTable
where Type_Here = 'D'
and What_I_Need = 2
March 30, 2011 at 1:51 pm
Whts your expected result out of your sample data?
March 30, 2011 at 2:19 pm
How about this?
select * , Rn = NTILE(2) OVER(PARTITION BY Type_Here ORDER BY ( case when Tab_id%2 = 0 THEN 1
else 0 end ) ) From @TheTable
order by Tab_id
March 30, 2011 at 2:29 pm
I need it to update the Put_it_Here column
March 30, 2011 at 2:35 pm
mfink (3/30/2011)
I need it to update the Put_it_Here column
So lazy, are we ??
Try this:
UPDATE @TheTable
SET put_IT_HERE = NTILE(2) OVER(PARTITION BY Type_Here ORDER BY ( CASE WHEN Tab_id%2 = 0 THEN 1 ELSE 0 END ) )
FROM @TheTable
First check whatever the SELECT statemtnt in the post above outputs works for your requirement. Thn UPDATE it using the above statement.
March 30, 2011 at 2:45 pm
msg 4108 Level 15 state 1 line 41
Windowed functions can only appear in the select or order by clause
I have tried this is does not work in an update statement
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy