SQL UPDATE statement based on number of rows returned from SELECT subquery

Viewed 32

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

0 Answers
Related