Question 20772 in Mysql Question posted in
The official MySQL documentation can be found here.

Update a column value if two specific conditions are met in MySQL

I’m very much a newbie to programming.
I am trying to update my table column labelled ‘bonus’ with a new value of 505.99 if two conditions are met: if the givenname is Mary and their bonus is currently 155.99, or if their occupation is a Model and their bonus is also currently 155.99. 7 rows should be updated but only 1 is being updated.
The query looks like it should work to me so wondering what I am missing?
Looking for any pointers!
Thanks in advance

UPDATE customers
SET bonus = 505.99
WHERE occupation = 'Model' AND bonus = 155.99
OR givenname = 'Mary' AND bonus = 155.99;
Tags:

2

Answers


  1. 0

    Can you try to use parentheses like these?

    UPDATE customers
SET bonus = 505.99
WHERE
    (occupation = 'Model' AND bonus = 155.99)
    OR
    (givenname = 'Mary' AND bonus = 155.99);
    Login or Signup to reply.
  2. 0

    You should use AND and OR conditions properly when you use those simultaneously.

    Let us build your query :

    As we know we are going to set value wherever following holds true.

    1. Either occupation = 'Model' OR givenname = 'Mary'
      This should be written with OR together

    2. bonus = 155.99 : This we can add separately using AND in the select query.

    So; the correct condition to use is (occupation = 'Model' OR givenname = 'Mary') and bonus = 155.99;

    We can re-write the above query as :

    UPDATE customers
SET bonus = 505.99
WHERE bonus = 155.99
AND (occupation = 'Model' OR givenname = 'Mary');

    You can find more good examples here :

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search