0

We have a valid_phone column that just stores 1 or 0 for valid or not valid.

When the user changes their phone number we want that field to go back to 0 so they will have to validate their number again. If it's not changing we want to respect whatever value was there, so not always just assuming that if it didn't change set it to 1 because they may have not validated their phone yet so it should in that case stay at 0.

I saw a query that used a CASE and thought it may work for us but so far I have not gotten it to work.

UPDATE text2bid_users SET valid_phone = CASE
    WHEN phone NOT '0000' THEN '0'
END WHERE user_id = '1234'

I'm sure my syntax may be wrong or I may even be looking in the wrong place for a solution as I'm a front end guy just starting to dip into backend and database.

My thought on this is to see if phone is already set to the phone I'm about to update it with (so that wouldn't be hard-coded) and if it is not the same then set valid_phone to 0 and then outside of this name, email, address etc. will also update as well.

How would I get a query to do what we need it to do here?

I might be updating other values too, so I don't want it to not update the other fields if the phone comes through as the same. I suppose I could run two queries but I feel like I should be able to do it in one query. I may be wrong.

1
UPDATE text2bid_users
SET valid_phone = CASE WHEN phone != '0000' THEN 0 else valid_phone END,
phone = '0000'
WHERE user_id = 1;

http://sqlfiddle.com/#!9/24784/1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.