CODEWITHSUNDEEP
mysqlsql
Michael Niño
Michael Niño

Reputation: 467

SQL: How to update column with unique values

Below is a MySQL query:

SELECT 
    COUNT(*) AS counted, employer_group
FROM
    employer_survey
GROUP BY employer_group
HAVING counted > 1;

Before I alter table definition for employer_group to unique, I need to create an UPDATE statement to CONCAT() the value of created_dt to employer_group so the alter table will not fail because of values.

How do I do this? I am unable to return id column because I am using GROUP BY and HAVING.

I should mention that I want the id column returned so I may use the above SELECT with an IN clause in my UPDATE statement. This may not be the best approach.

Upvotes: 1

Views: 5570

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can do this with join:

update employer_survey es join
       (select es2.employer_group
        from employer_survey es2
        group by es2.employer_group
        having count(*) > 1
       ) eg
       on es.employer_group = eg.employer_group
    set es.employer_group = concat_ws(' ', es.employer_group, es.created_dt);

Upvotes: 3

Related Questions