query needed to swap column values for duplicate

xcavin

userid status phone_number
1 0 111
2 1 111
3 0 222
4 1 222
5 0 333
6 1 444

For the above table I want write a SQL query to swap the value of "status" column for duplicate rows. Duplicates can be identified if phone number is same. The output would look like as below (affecting only 1,2,3 and 4).

userid status phone_number
1 1 111
2 0 111
3 1 222
4 0 222
5 0 333
6 1 444

Any help is appreciated. I always find it always hard to write even a simple SQL query :(.

_Damian S_

You will need to do it in two parts... First, create a query that returns duplicate phone numbers, like this:

select phone_number
from TABLENAME
group by phone_number
having count(phone_number) > 1

Then, you can use some white-man-magic to reverse your statii (assuming you want to switch 0 for 1, and 1 for 0), like this:

update TABLENAME
set status = abs(status - 1) -- This means if status is 0, you get abs(-1) which = 1
where phone_number in
(
select phone_number
from TABLENAME
group by phone_number
having count(phone_number) > 1
)

See how we use the first query to restrict the update query?

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!