We help IT Professionals succeed at work.

update field on random basis

johnnyg123
johnnyg123 asked
on
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
Comment
Watch Question

Top Expert 2012
Commented:
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

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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.

Author

Commented:
Thanks Anthony for syntax

Thanks Paul for heads up