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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.