mysql case function

Question

I have following query and it is updating MAC address which are missing hyphnes but for the other who alerady have them it is changing them to null. Try to update values in one column.

UPDATE mytable SET MAC =
CASE
 WHEN MAC NOT LIKE '%-%' 
  THEN UPPER(CONCAT(SUBSTR(MAC,1,2),'-', SUBSTR(MAC,3,2),'-', SUBSTR(MAC,5,2),'-', SUBSTR(MAC,7,2),'-', SUBSTR(MAC,9,2),'-', SUBSTR(MAC,11,2)))
 END;

Any suggestions woild be great.

Thanks

Michael Berkowski · Accepted Answer · 2013-01-11 17:31:59Z

3

Just add an ELSE case which sets the value to its current value:

UPDATE mytable SET MAC =
CASE
 WHEN MAC NOT LIKE '%-%' 
  THEN UPPER(CONCAT(SUBSTR(MAC,1,2),'-', SUBSTR(MAC,3,2),'-', SUBSTR(MAC,5,2),'-', SUBSTR(MAC,7,2),'-', SUBSTR(MAC,9,2),'-', SUBSTR(MAC,11,2)))
  /* Else case sets it to its current value to avoid NULL */
  ELSE MAC
 END;

It's also possible to use a WHERE clause so rows that should not be updated are not matched in the first place. That is probably the more appropriate action to take in this instance, unless you have other conditions to apply in the CASE

UPDATE mytable 
SET MAC = UPPER(CONCAT(SUBSTR(MAC,1,2),'-', SUBSTR(MAC,3,2),'-', SUBSTR(MAC,5,2),'-', SUBSTR(MAC,7,2),'-', SUBSTR(MAC,9,2),'-', SUBSTR(MAC,11,2)))
WHERE MAC NOT LIKE '%-%'

answered Jan 11, 2013 at 17:31

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom · Accepted Answer · 2013-01-11 17:34:41Z

1

UPDATE mytable 

SET MAC = UPPER(CONCAT(SUBSTR(MAC,1,2),'-', SUBSTR(MAC,3,2),'-', SUBSTR(MAC,5,2),'-', SUBSTR(MAC,7,2),'-', SUBSTR(MAC,9,2),'-', SUBSTR(MAC,11,2)))

WHERE  MAC NOT LIKE '%-%'

answered Jan 11, 2013 at 17:34

Tom

6,6633 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

mysql case function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related