UPDATE MySQL table with HAVING clause

Question

I created slug field for a table and they need to be unique but some of them aren't so i think i can add random characters at the end of them to make them unique.

So this is my SELECT query:

SELECT slug,count(*) as num FROM table GROUP BY slug HAVING num > 1

This is the UPDATE query:

UPDATE table SET slug = CONCAT(slug,'-',SUBSTRING(MD5(NOW()),1,2))

I couldn't manage to combine this 2 queries.

Answer 1

You can use a JOIN:

UPDATE mytable AS m
JOIN (SELECT slug
      FROM mytable 
      GROUP BY slug 
      HAVING COUNT(*) > 1) t
ON m.slug = t.slug      
SET m.slug = CONCAT(m.slug,'-',SUBSTRING(MD5(NOW()),1,2))

However the problem with the above is that it does not generate unique string values.

Demo here

One way to get around this, is to use row numbers calculated by variables:

UPDATE mytable AS m
CROSS JOIN (SELECT @row_number:=0, @slug := '') AS vars 
JOIN (
   SELECT slug
   FROM mytable 
   GROUP BY slug 
   HAVING COUNT(*) > 1 ) AS t ON m.slug = t.slug       
SET m.slug = CONCAT(m.slug, 
                    '-',
                    IF (@slug = m.slug,
                         IF (@slug := m.slug,@row_number:=@row_number+1, 
                                             @row_number:=@row_number+1),
                         IF (@slug := m.slug, @row_number:=1, 
                                              @row_number:=1)) 
                   )

Demo here

Answer 2

Try:

UPDATE t ,
(  SELECT slug FROM t 
  GROUP BY slug HAVING count(*) > 1
) x
SET  t.slug = CONCAT(t.slug,'-',SUBSTRING(MD5(rand()),1,2))
WHERE t.slug = x.slug
;

Demo: http://sqlfiddle.com/#!9/01c68/1

I am using rand() instead of now() because the latter won't give unique strings.