Same key with different flags-CodePudding

I have a table as an input which is the following:

Key	Flag
123	Yes
123	No
567	No
876	Yes

When I have two same keys with different values in the Flag field, I want to keep only the one key with the Flag = Mixed

Desired Output

Key	Flag
123	Mixed
567	No
876	Yes

Could you please help me to figure it out, by using SQL?

CodePudding user response：

Do this in two steps:

Replace the flag in one of the rows for each key that has both values with Mixed.
Delete the other row for a key that has a Mixed flag.

UPDATE yourTable AS t1
JOIN yourTable AS t2 ON t1.key = t2.key
SET t1.flag = 'Mixed'
WHERE t1.flag = 'Yes' AND t2.flag = 'No';

DELETE t1 
FROM yourTable AS t1
JOIN yourTable AS t2 ON t1.key = t2.key
WHERE t1.flag = 'No' AND t2.flag = 'Mixed';

The first query replaces Yes with Mixed in each pair, the second query deletes the corresponding No rows.

Put a transaction around this so that it will be atomic.

CodePudding user response：

Assuming you only want to have this output in a query rather than altering the table, You can employ the group_concat() function. This will put your distinct flags into one column with a comma delimiter. You can then put this into a case statement to get your desired output.

select `key`,

        group_concat(flag) flag

from temp_db.table

group by `key`

Key	Flag
123	Yes, No

select `key`

      ,case when flag = 'Yes, No' then 'Mixed'

            else flag end as flag

from (select `key`,

      group_concat(flag) flag

      from temp_db.table

      group by `key`)

CodePudding user response：

One option is to create a new table from this table by using a conditional select query, and rename new table to this current one(namely t1) after dropping the table while adding a primary key constraint such as

CREATE TABLE t2 AS
SELECT `Key`, CASE WHEN COUNT(`Key`)=2 THEN 'Mixed' ELSE MAX(Flag) END AS Flag
  FROM t1
 GROUP BY `Key`;

DROP TABLE t1;

RENAME TABLE t2 TO t1;

ALTER TABLE t1 ADD PRIMARY KEY(`Key`);

where the data throughout the whole table is assumed to have only single or double values for each Key column's value

Demo