samedi 25 juillet 2015

SQL/PHP swap values in multiple rows

Here's an example database:

type | name
-----------------------
B    | John Doe
B    | John Smith
B    | Little Johnny
ZZ   | Mary Jane
ZZ   | Mary's Mom
X    | Tom

I want to be able to swap all instances of two given values. Just to further my example, let's swap B and X.

type | name
-----------------------
X    | John Doe
X    | John Smith
X    | Little Johnny
ZZ   | Mary Jane
ZZ   | Mary's Mom
B    | Tom

This can't be done with two UPDATE commands because it'll end up setting all of the rows to the same type.

UPDATE people SET type="B" WHERE type="X"
UPDATE people SET type="X" WHERE type="B"

Yields this unfortunate result:

type            | name
-----------------------------------
X               | John Doe
X               | John Smith
X               | Little Johnny
ZZ              | Mary Jane
ZZ              | Mary's Mom
X *should be B* | Tom

Aucun commentaire:

Enregistrer un commentaire