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