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)

technical database sql mysql development code opensource example engine css

Truncating big MySQL (InnoDB) tables

Truncating big tables, with millions of rows, in InnoDB can be really slow. Luckily there is a s...

MySQL 5.0 Reference Manual

MySQL is one of the most popular databases for websites. The main reasons are price and speed :-...

MySQL AB :: MySQL Documentation

The MySDQL Reference Manual for almost all MySQL versions that are now in use.

SQLAlchemy - The Database Toolkit for Python

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application develope...

Sphinx - Free open-source SQL full-text search engine

Sphinx is a full-text search engine, distributed under GPL version 2. Commercial license is also ...

MonetDB - Query Processing at Light Speed

MonetDB/SQL and MonetDB/XQuery, fast query language support in an open-source column-orienated da...

Multi-Threaded Programming With POSIX Threads

This tutorial is an attempt to help you become familiar with multi-threaded programming with the ...

PrimeBase Technologies - PBXT Storage Engine for MySQL

PrimeBase XT (PBXT) is a transactional storage engine for MySQL. As illustrated below, a MySQL st...

GeSHi - Generic Syntax Highlighter

GeSHi - Generic Syntax Highlighter for php. Highlight many languages, including PHP, CSS, HTML, S...

The Man in Blue > footerStickAlt: A more robust method of positioning a footer

The Man in Blue showcases the writing and Web design of Cameron Adams.

This is a method t...

technical code example sql css development generic highlighting beautifier highlighter

Truncating big MySQL (InnoDB) tables

Truncating big tables, with millions of rows, in InnoDB can be really slow. Luckily there is a s...

Multi-Threaded Programming With POSIX Threads

This tutorial is an attempt to help you become familiar with multi-threaded programming with the ...

GeSHi - Generic Syntax Highlighter

GeSHi - Generic Syntax Highlighter for php. Highlight many languages, including PHP, CSS, HTML, S...

The Man in Blue > footerStickAlt: A more robust method of positioning a footer

The Man in Blue showcases the writing and Web design of Cameron Adams.

This is a method t...

development technical database mysql sql python source code activerecord documentation

MySQL AB :: MySQL Documentation

The MySDQL Reference Manual for almost all MySQL versions that are now in use.

SQLAlchemy - The Database Toolkit for Python

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application develope...

Sphinx - Free open-source SQL full-text search engine

Sphinx is a full-text search engine, distributed under GPL version 2. Commercial license is also ...

Multi-Threaded Programming With POSIX Threads

This tutorial is an attempt to help you become familiar with multi-threaded programming with the ...

opensource sql mysql engine technical software database streaming blob storage engine

Sphinx - Free open-source SQL full-text search engine

Sphinx is a full-text search engine, distributed under GPL version 2. Commercial license is also ...

MonetDB - Query Processing at Light Speed

MonetDB/SQL and MonetDB/XQuery, fast query language support in an open-source column-orienated da...

PrimeBase Technologies - PBXT Storage Engine for MySQL

PrimeBase XT (PBXT) is a transactional storage engine for MySQL. As illustrated below, a MySQL st...

MarcWorrell.com/ created on 2007-12-11 17:49:56/ modified on 2007-12-11 18:19:22/ mail me at