update field on random basis

Here is some sample data I have from a table named  MailList  (SQL Server 2008)
(For space purposes I have left out Street address and Zip Code)


PersonID   FirstName     LastName       City                      State       MailTo
1                  Al                   Doe                  Dallas                  TX             Y
2                  Jill                   Doe                  Dallas                  TX             Y    
3                  Jane               Doe                  Austin                  TX             Y      
4                  John               Smith               Houston            TX             Y      
5                  Ron                White               New York           NY            Y
6                  Beth               Doe                  Los Angeles       CA            Y
7                  Bart               Doe                  Rochester          NY            Y    
8                  Jim                  Jones               Burbank             CA            Y  
9                  Jack                Black               Annaheim           CA           Y      

In an effort to support a marketing mail campaign I need to take a random number of records for a given state and update the mailto flag from Y to N

Lets say the numbers are as follows

TX= 2

CA = 1



After the query

PersonID   FirstName     LastName       City                      State       MailTo
1                  Al                   Doe                  Dallas                  TX             N
2                  Jill                   Doe                  Dallas                  TX             Y    
3                  Jane               Doe                  Austin                  TX             Y      
4                  John               Smith               Houston            TX             N      
5                  Ron                White               New York           NY            Y
6                  Beth               Doe                  Los Angeles       CA            N
7                  Bart               Doe                  Rochester          NY            Y    
8                  Jim                  Jones               Burbank             CA            Y  
9                  Jack                Black               Annaheim           CA           Y      


Not quite sure the best way to approach this
johnnyg123Asked:
Who is Participating?
 
Anthony PerkinsCommented:
Something like this perhaps:
UPDATE  m
SET     MailTo = 'N'
FROM    MailList m
        INNER JOIN (SELECT TOP (20) PERCENT
                            PersonID
                    FROM    MailList
                    ORDER BY NEWID()
                   ) m2 ON m.PersonID = m2.PersonID

Open in new window


This is how I tested it:
DECLARE @MailList table (PersonID integer NOT NULL, FirstName varchar(50), LastName varchar(50), City varchar(50), [State] char(2), MailTo char(1))

INSERT @MailList (PersonID, FirstName, LastName,  City,  [State], MailTo)
VALUES (1, 'Al', 'Doe', 'Dallas', 'TX', 'Y'),
(2, 'Jill', 'Doe', 'Dallas', 'TX', 'Y'),
(3, 'Jane', 'Doe', 'Austin', 'TX', 'Y'),
(4, 'John', 'Smith', 'Houston', 'TX', 'Y'),
(5, 'Ron', 'White', 'New York', 'NY', 'Y'),
(6, 'Beth', 'Doe', 'Los Angeles', 'CA', 'Y'),
(7, 'Bart', 'Doe', 'Rochester', 'NY', 'Y'),
(8, 'Jim', 'Jones', 'Burbank', 'CA', 'Y'),
(9, 'Jack', 'Black', 'Annaheim', 'CA', 'Y')

UPDATE  m
SET     MailTo = 'N'
FROM    @MailList m
        INNER JOIN (SELECT TOP (20) PERCENT
                            PersonID
                    FROM    @MailList
                    ORDER BY NEWID()
                   ) m2 ON m.PersonID = m2.PersonID

SELECT *
FROM @MailList

Open in new window

0
 
PortletPaulfreelancerCommented:
Mmmmm

my reaction to this would be to do this using inserts into another table, not by updating that field.

e.g. a table like this:
PersonID CampainID
2                1
3                1
5                1
7                1
8                1
9                1
(i.e. providing the the equivalent of the 'Y')

this way you can trace who each each "campaign" has contacted (and/or who has not been contacted)

another table would hold attribute of the campaign name/what for/date etc.
0
 
johnnyg123Author Commented:
Thanks Anthony for syntax

Thanks Paul for heads up
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.