< WhatWebWhat Search >

Swapping two columns in MySQL

Sometimes the edges in our MySQL database are referring into the wrong direction. In those cases we have to swap the subject and the object id of those edges. How to do that in one SQL query?

It turns out that it is possible, with some combined effort of me and Guilherme Lopes we found a nice SQL statement that does exactly this.

You can exchange the two values by using a temporary variable, which stores the old value of one of the columns. It is assigned in the where clause of the update statement.

This is the simplified SQL statement:

UPDATE `sometable`
SET `a` = `b`,
    `b` = @olda
WHERE (@olda := `a`)

And all the a's and b's in the table are swapped!

And this is the code we use to mirror edges in anyMeta:

UPDATE any_edge
SET edg_subject_id_ref = edg_object_id_ref,
    edg_object_id_ref = @subj,
    edg_prim_subject_id_ref = edg_prim_object_id_ref,
    edg_prim_object_id_ref = @psubj
WHERE edg_predicate_id_ref = (
                SELECT thg_id FROM any_thing
                WHERE thg_kind = 'ROLE' AND thg_symbolic_name = 'MEMBER')
  AND (@subj := edg_subject_id_ref)
  AND (@psubj := edg_prim_subject_id_ref)

Articles Tuesday, December 11, 2007