CODEWITHSUNDEEP
mysqlsql
user2140285
user2140285

Reputation: 173

Update column based on duplicate columns

I have 2 tables:

account

+----+---------------------+
| Id |        Email        |
+----+---------------------+
|  1 | "[email protected]"  |
|  2 | "[email protected]" |
|  3 | "[email protected]" |
|  4 | "[email protected]"  |
+----+---------------------+

character

+----+-----------+
| Id | AccountId |
+----+-----------+
|  1 |         1 |
|  2 |         1 |
|  3 |         2 |
|  4 |         3 |
|  5 |         4 |
+----+-----------+

character.AccountId is a FK to account.Id. Both Id columns are PK's to their respective tables.

I need to update the character table such that the new AccountId matches a row in account with the lowest account's Id but with the same Email as the currently set AccountId.

For example, in the mock data presented above all accounts have unique emails except account Id 1 and 4, they both share [email protected] as email.

This means that after the update, the rows in the character table should stay the same except for the row with Id = 5, this row has an AccountId = 4 and this account shares an email with an account that has a lower account Id, namely Id 1. So the result output should be :

+----+-----------+
| Id | AccountId |
+----+-----------+
|  1 |         1 |
|  2 |         1 |
|  3 |         2 |
|  4 |         3 |
|  5 |         1 |
+----+-----------+

After the operation.

I've got this to work with a TRIGGER BEFORE INSERT on character to check if there are lower accountIds but can't get this to work with a simple UPDATE:

UPDATE `character` SET `AccountId` = (SELECT MIN(Id) FROM `account` WHERE ?);

I've thought of maybe making a temporary table to store the account Id and the email with a GROUP BY email but this also fails. It being MySQL I can't use MERGE either.

Upvotes: 0

Views: 82

Answers (2)

ysth
ysth

Reputation: 98423

This is fairly straightforward without window functions, too; you want to find accounts with the same email and lower ids (the a2 join) and make sure you've got the lowest (the a3 join):

update `character` c
join account a on a.id=c.account_id
join account a2 on a2.email=a.email and a2.id < a.id
left join account a3 on a3.email=a2.email and a3.id < a2.id
set c.account_id=a2.id
where a3.id is null;

(As usual, the left join ... where ... is null could be replaced with a where not exists (...), if you prefer the query to show the intent more clearly instead of the probable query plan more clearly.)

fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use a window function to get the minimum id per email. Then just join that in:

UPDATE character c JOIN
       (SELECT a.*,
               MIN(id) OVER (PARTITION BY a.email) as min_id
        FROM account a
       ) a
       ON c.accountId = a.id
    SET c.accountId = a.min_id
    WHERE c.accountId <> a.min_id;

Upvotes: 2

Related Questions