CODEWITHSUNDEEP
mysql
soft genic
soft genic

Reputation: 2056

Guessing gender from other names in same table

Following is my SQLFIDDLE in which you'll see there are names with place ABC who have genders and there are same names but with different place DEF which donot have any gender. What i have been trying to do is to populate the gender of DEF based on the gender of the similar name of ABC. Kindly let me know how can i do that (IN same table updation) thanks,

FIDDLE: http://sqlfiddle.com/#!2/e6b05/1

Upvotes: 0

Views: 109

Answers (1)

maček
maček

Reputation: 77788

This should do the trick

UPDATE addr AS a
JOIN addr AS b
  ON  b.Name = a.Name
  AND b.place = 'ABC'
  AND a.place = 'DEF'
SET a.gender = b.gender
WHERE a.gender = 'N/A'

If place doesn't matter

UPDATE addr AS a
JOIN addr AS b
  ON  b.Name = a.Name
  AND b.gender <> 'N/A' 
SET a.gender = b.gender
WHERE a.gender = 'N/A'

Upvotes: 1

Related Questions