how to update gender = M or F in a table that has gender values as null

Question

how to update gender = M or F in a table that has gender values as null

db8

Right there with Babe

Points: 728
More actions
April 20, 2017 at 1:06 pm

#342531

Hi
Any help on how to update gender = M or F in a table that has gender values as null
need a random insert in to table.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

db8 - Thursday, April 20, 2017 1:06 PM
Hi
Any help on how to update gender = M or F in a table that has gender values as null
need a random insert in to table.

How would you define if it should be M or F?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DesNorton SSC-Insane Points: 24381 More actions · Answer 2

I believe that this will meet your requirements
WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N)) , NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4) UPDATE src SET Gender = CASE WHEN ABS(CHECKSUM(NEWID())) %2 = 0 THEN 'F' ELSE 'M' END FROM NUMS AS NM INNER JOIN <TableName> AS src ON NM.N = src.id WHERE src.Gender IS NULL;

How to post data/code on a forum to get the best help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

General advice - don't. There's no way from the name you're going to get the gender right. If that's a customers table, then doing so is sooner or later going to cause problems, sending a letter with the wrong title, for example (and titles are a whole nother pile of potential problems)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 4

I agree with GilaMonster. Don't update user data without the help of a data steward.

However, if you're simply trying to mock up some test data, this is a simple approach I've used. Use a function against data in the table to generate virtually random results.
CREATE TABLE Star (FName VARCHAR(10) NOT NULL, Gender CHAR(1) NULL)

INSERT INTO Star (FName) VALUES ('Seamus'), ('Cassie'), ('Maria'), ('Jerrod'), ('Xander'), ('Edmond')

UPDATE Star SET Gender = CASE ASCII(s.FName)%2 WHEN 0 THEN 'M' ELSE 'F' END FROM Star s

SELECT Fname, Gender FROM Star

The data set in the results illustrates GilaMonster's point. Some of the data looks right, but any unnoticed inconsistencies will cause headaches later.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Kaye Cahs - Thursday, April 20, 2017 1:55 PM
However, if you're simply trying to mock up some test data, this is a simple approach I've used. Use a function against data in the table to generate virtually random results.
CREATE TABLE Star (FName VARCHAR(10) NOT NULL, Gender CHAR(1) NULL) INSERT INTO Star (FName) VALUES ('Seamus'), ('Cassie'), ('Maria'), ('Jerrod'), ('Xander'), ('Edmond')
UPDATE Star SET Gender = CASE ASCII(s.FName)%2 WHEN 0 THEN 'M' ELSE 'F' END FROM Star s

Or

UPDATE Star SET Gender = CASE WHEN RAND(CHECKSUM(NEWID())) > 0.5 THEN 'M' ELSE 'F' END

Completely random assignment. Fine for test data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

db8 Right there with Babe Points: 728 More actions · Answer 6

@GilaMonster
Hi sir, i agree with you , but i am using this data only for test purposes.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Personally, I believe each of those images should be redacted. The data looks to be real enough that it could cause problems.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Neil Burton SSC-Insane Points: 22998 More actions · Answer 8

SQLRNNR - Friday, April 21, 2017 1:04 AM
Personally, I believe each of those images should be redacted. The data looks to be real enough that it could cause problems.

It looks real but that's certainly not a UK phone number and I'm pretty sure Connecticut isn't on this side of the pond.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Neil Burton - Friday, April 21, 2017 1:15 AM
SQLRNNR - Friday, April 21, 2017 1:04 AM
Personally, I believe each of those images should be redacted. The data looks to be real enough that it could cause problems.
It looks real but that's certainly not a UK phone number and I'm pretty sure Connecticut isn't on this side of the pond.

True the country, city and state seem to be randomized. The combination of the phone number with DOB was too eerily close to info I was able to google.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events