asked on

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

ASKER CERTIFIED SOLUTION