Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I just had to deal with the same and I'll summarize my findings.</p> <ol> <li><p>The <code>UPDATE table SET X=Y, Y=X</code> approach obviously doesn't work, as it'll just set both values to Y.</p></li> <li><p>Here's a method that uses a temporary variable. Thanks to Antony from the comments of <a href="http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/" rel="noreferrer">http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/</a> for the "IS NOT NULL" tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn't swap the values if one of them is NULL. Use method #3 that doesn't have this limitation.</p> <p><code>UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;</code></p></li> <li><p>This method was offered by Dipin in, yet again, the comments of <a href="http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/" rel="noreferrer">http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/</a>. I think it’s the most elegant and clean solution. It works with both NULL and non-NULL values.</p> <p><code>UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;</code></p></li> <li><p>Another approach I came up with that seems to work:</p> <p><code>UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;</code></p></li> </ol> <p>Essentially, the 1st table is the one getting updated and the 2nd one is used to pull the old data from.<br/> Note that this approach requires a primary key to be present.</p> <p>This is my test schema:</p> <pre><code>CREATE TABLE `swap_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `x` varchar(255) DEFAULT NULL, `y` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO `swap_test` VALUES ('1', 'a', '10'); INSERT INTO `swap_test` VALUES ('2', NULL, '20'); INSERT INTO `swap_test` VALUES ('3', 'c', NULL); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSwapping column values in MySQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPaul Dixon
    1. USArtem Russakovskii
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSwapping column values in MySQL
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAs noted in MySQL docs, It's not safe to assign and read variables in a single statement. The order of operations is not guaranteed. So the only safe method is #4
      singulars
      1. PO
      1. USAMIB
    2. COOption 4 worked for me. You can obviously add more conditions to the where clause if you need to swap the columns for only some rows.
      singulars
      1. PO
      1. USBrad Campbell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload