vankuik.nl RecentChanges 2014-10-23 XML TXT

SQL

Oracle's decode in MySQL

I have an Oracle background where a decode instruction was never far away. Nowadays I use MySQL for lots of stuff and when I needed a decode, I was stuck for a few minutes.

The query below changes a table which previously had both a 'note' and a 'description' field. These were combined with the following query in a new field 'descr'. After the update, the old fields could both be dropped from the table.

 update tscu
 set descr = 
 (  case
         when length(description) = 0
              and length(note) > 0
         then note
         when length(note) = 0
         then description
         when length(description) > 0
              and length(note) > 0
         then concat(description, ' Note: ', note)
    end
 )