3

I have 10 rows.first 5 rows is gender column with "male". second 5 rows gender column with "female".

I want mysql query to change where column "male" into "female" and "female" into "male".

Thanks for answering...

If i tried male into female . all fields will be male.

phpstarter
  • 41
  • 1
  • ___Step 1:___ Change `male` into `temp`. ___Step 2:___ Change `female` into `male` ___Step 3:___ Change `temp` into `female` – RiggsFolly Jan 04 '23 at 17:56
  • The linked answer should show how to do this via a MySQL query, but you could also do this in Laravel/PHP. `$maleRecords = DB::table('your_table')->where('gender', 'male')->get();` and `$femaleRecords = DB::table('your_table')->where('gender', 'female')->get();`. Then, simply loop both of those Collections and swap their `gender` (obviously not efficient for large datasets, but since you only have 10 records, that's trivial) – Tim Lewis Jan 04 '23 at 17:58
  • 1
    Something like `replace(replace(replace(column, 'female', 'temp'), 'male' ,'female'), 'temp', 'male')` should do it. Can use that in a `select` if it is to be done when running or in an update if DB values actually should change. Not really a PHP or laravel question.. – user3783243 Jan 04 '23 at 18:02
  • @RiggsFolly Changing `male` first will create `fetemp` for the `female` records, unless boundaries are used. – user3783243 Jan 04 '23 at 18:04
  • @user3783243 Only if you were to run a very ODD query, we are not using an editor on a text file here – RiggsFolly Jan 04 '23 at 18:25
  • 2
    not a duplicate of the linked question; that is about swapping two distinct columns, not swapping two *values* of the same column – ysth Jan 04 '23 at 18:29
  • this is simply `update mysterytablename set gender = case when gender='male' then 'female' when gender='female' then 'male' else gender end` – ysth Jan 04 '23 at 18:30
  • ```... SET sex = 3 - sex```, but leetcode does not accept this solution. – Akina Jan 04 '23 at 19:08

0 Answers0