Update field with new string made from characters in existing value

field is meta_value and current value is like 01/01/2006

i want to change it to a string 20060101 so thought i could get the value and update it with the characters in the correct order.

ie

UPDATE wp_postmeta SET meta_value = meta_value[9]meta_value[8]meta_value[7]meta_value[6]

Assuming that meta_value is always dd/mm/yyyy and you are looking for yyyymmdd (which isn't clear), try this:

UPDATE wp_postmeta 
SET meta_value = CONCAT(SUBSTRING(meta_value FROM 7), 
     SUBSTRING(meta_value, 4, 2), 
     SUBSTRING(meta_value, 1, 2));

Run that as a SELECT first to verifying the output before update and make sure it's what you're really looking for.

StackExchange Database Administrators Q#29352, answer score: 6