I have a table with cols ID, and account type(int)
and I want to update account type to 3 for all rows, except one where given an ID there is more than one row with the same account type. so if I have
ID --- account_type
1 --- 2
1 --- 2
2 --- 1
2 --- 2
2 --- 3
I want to change one of the rows with id = 1 to have account type = 3 but leave the other one at 2 so I would like to return for the above example.
ID --- account_type
1 --- 2
1 --- 3
2 --- 1
2 --- 2
2 --- 3
I tried
UPDATE myTable
SET account_type = 3
WHERE 1 < (
SELECT COUNT(*)
FROM myTable
GROUP BY ID, account_type
HAVING COUNT(*) > 1);
but this updated every row in my db instead of just the one row with the duplicate account type so I know I did something wrong there. and this statement would set both rows with id=1 to have account_type =3 instead of just one row. How would I accomplish this?
EDIT: I think I can fix the problem of only updating one row with:
UPDATE myTable p1
SET account_type = 3
WHERE 1 < (
SELECT COUNT(*)
FROM myTable p2
WHERE p1.primaryKey > p2.primaryKey
GROUP BY ID, account_type
HAVING COUNT(*) > 1);
but I'm still not sure why this updating every row in the db instead of the one with the duplicate account_type