sql puzzle.

• ekant_alone

  SSCrazy

  Points: 2423

  More actions

  October 17, 2011 at 12:12 pm

  #261539

  I have a table enployee and a column gender.

  The possible values in the table are 'm' or 'f'

  In this puzzle i need to swap m with f and f with m in just one sql statement.

  Any ideas?

• Jared

  SSC Guru

  Points: 61793

  More actions

  October 17, 2011 at 12:17 pm

  #1395515

  ekant_alone (10/17/2011)

  ---

  I have a table enployee and a column gender.

  The possible values in the table are 'm' or 'f'

  In this puzzle i need to swap m with f and f with m in just one sql statement.

  Any ideas?

  UPDATE employee

  SET gender = new.gender

  FROM employee e

  INNER JOIN (SELECT EmpId, CASE WHEN gender = 'f' THEN 'm' WHEN gender = 'm' THEN 'f' END) new

  ON e.Empid = new.empId

  untested

  Thanks,

  Jared

  Jared
  CE - Microsoft

• venoym

  SSCarpal Tunnel

  Points: 4161

  More actions

  October 17, 2011 at 12:18 pm

  #1395516

  Sounds like a home work question, so this is a very very basic SQL update with case statement. You should be able to start there.

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  October 17, 2011 at 12:24 pm

  #1395521

  ekant_alone (10/17/2011)

  ---

  I have a table enployee and a column gender.

  The possible values in the table are 'm' or 'f'

  In this puzzle i need to swap m with f and f with m in just one sql statement.

  Any ideas?

  CREATE table enployee (gender CHAR(1), oldgender CHAR(1))

  INSERT INTO enployee (gender, oldgender) SELECT 'F', 'F'

  INSERT INTO enployee (gender, oldgender) SELECT 'F', 'F'

  INSERT INTO enployee (gender, oldgender) SELECT 'M', 'M'

  INSERT INTO enployee (gender, oldgender) SELECT 'F', 'F'

  INSERT INTO enployee (gender, oldgender) SELECT 'M', 'M'

  INSERT INTO enployee (gender, oldgender) SELECT 'M', 'M'

  SELECT * FROM enployee

  UPDATE enployee SET gender = CASE WHEN gender = 'F' THEN 'M' WHEN gender = 'M' THEN 'F' END

  SELECT * FROM enployee

  You won't learn without putting in some effort.

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Jared

  SSC Guru

  Points: 61793

  More actions

  October 17, 2011 at 12:28 pm

  #1395523

  ChrisM@home (10/17/2011)

  ---

  ekant_alone (10/17/2011)

  ---

  I have a table enployee and a column gender.

  The possible values in the table are 'm' or 'f'

  In this puzzle i need to swap m with f and f with m in just one sql statement.

  Any ideas?

  CREATE table enployee (gender CHAR(1), oldgender CHAR(1))

  INSERT INTO enployee (gender, oldgender) SELECT 'F', 'F'

  INSERT INTO enployee (gender, oldgender) SELECT 'F', 'F'

  INSERT INTO enployee (gender, oldgender) SELECT 'M', 'M'

  INSERT INTO enployee (gender, oldgender) SELECT 'F', 'F'

  INSERT INTO enployee (gender, oldgender) SELECT 'M', 'M'

  INSERT INTO enployee (gender, oldgender) SELECT 'M', 'M'

  SELECT * FROM enployee

  UPDATE enployee SET gender = CASE WHEN gender = 'F' THEN 'M' WHEN gender = 'M' THEN 'F' END

  SELECT * FROM enployee

  You won't learn without putting in some effort.

  Yeah... not sure why I did it the way I did. Ugh, Mondays... 😉

  Jared

  Jared
  CE - Microsoft