Swapping Database Column Values (mySQL)

Software Developers know their away around SQL – it is a tool of the trade (as it were!), however today I had to revert to google to find the SQL to swap the values of 2 columns within a table. Luckily I found this interesting (and very handy) article that explains just how to do it:

 

http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/

 

I had to reverse the relationships between drupal taxonomy terms, this was to be done by reversing the values of the tid1 and tid2 columns of the term_relation table, this code did the trick for me:

 

[code]
UPDATE term_relation SET tid1=tid2, tid2=@temp WHERE (@temp:=tid1) IS NOT NULL;
[/code]

 

If I had to manually reverse all of the taxonomy relationships via the drupal admin interface it would have taken _forever_!

 

PS. If, for a given taxonomy term id, you need to get a list of the related taxonomy terms, just call:
[code]taxonomy_get_related()[/code] although be warned that the concept of related terms is to be removed in drupal 7!

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *