Mysql – Update a valid column when another column has changed

caseMySQLupdate

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.

Best Answer

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

Related Solutions

Mysql – Update a date_modified MySQL Record Field ONLY when other fields have changed using CASE statement not working

Below is my final working solution. It turns out that moving this...

date_modified = (CASE
                WHEN name <> values(name)
                OR description <> values(description)
                OR status <> values(status)
                OR type <> values(type)
                OR priority <> values(priority)
                  THEN UTC_TIMESTAMP()
                  ELSE date_modified
            END),

to the top of the list, above these fields...

name='$name',
description='$description',
status='$status',
priority='$priority',
type='$type',

So the final solution looks like this below...

$sql = "
    INSERT INTO
        $this->tasksDbTableName(task_id, project_id, name, description, status, priority, type, date_entered, date_modified, sort_order, heading)
    VALUES
        ('$taskId', '$projectId', '$name', '$description', '$status', '$priority', '$type', UTC_TIMESTAMP(), UTC_TIMESTAMP(), '$sort_order', '$heading')
    ON DUPLICATE KEY UPDATE
        date_modified = (CASE
            WHEN name <> values(name)
            OR description <> values(description)
            OR status <> values(status)
            OR type <> values(type)
            OR priority <> values(priority)
              THEN UTC_TIMESTAMP()
              ELSE date_modified
        END),
        name='$name',
        description='$description',
        status='$status',
        priority='$priority',
        type='$type',
        sort_order='$sort_order',
        heading='$heading'";

Just moving my CASE Statement to the top above the other set fields, made it start working correctly!

It almost seems as if these DB column fields name description status priority type were getting Updated before my Case statement could run which resulted in them always appearing to be the same as the DB fields and never any different so my Case statement was about useless. Moving it to the top, it now works 100% correctly.

Hopefully this will help someone with a similar problem someday as many people across 3 sites tried to fix it without success. Just moving it to the top did the trick though so the Order in this case very much matters! ] Thanks to all that have contributed their time to try and find a solution, I always appreciate the help I receive on the StackExchange network sites!

MySQL update one table from another joined many-to-many

This works - I had the JOIN wrong

UPDATE `events` 
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE 
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.

Best Answer

Related Solutions

Mysql – Update a date_modified MySQL Record Field ONLY when other fields have changed using CASE statement not working

MySQL update one table from another joined many-to-many

Related Question