CODEWITHSUNDEEP
mysqlsql
choloboy
choloboy

Reputation: 815

0 Rows Affected on UPDATE statement in MySQL

Hi I have an update statement where I am trying to reduce the quantity for the meta_key quantity from anything above 3 to 3 (the meta_value) and whenever I execute this I get "0 rows affected".

UPDATE usermeta 
INNER JOIN cov on cov.user_id = usermeta.user_id 
SET usermeta.meta_value = '3'
WHERE usermeta.meta_key = 'quantity' 
  AND usermeta.meta_value > '3'
  AND cov.end_date = '2017-05-18'
-- this is the query to see the rows I want changed   
SELECT cov.*, usermeta.meta_value
FROM cov
 INNER JOIN usermeta
    ON     cove.user_id = usermeta.user_id
       AND meta_key = 'quantity'
WHERE cov.end_date = '2017-05-18' and usermeta.meta_value > 3
ORDER BY end_date

Can anyone see what I am doing wrong? Thanks!

Upvotes: 0

Views: 780

Answers (3)

choloboy
choloboy

Reputation: 815

The problem with my update wasn't the code. It was because there were duplicate id's in the userid column. Pin pointing the invalid users and adding them to NOT IN in the WHERE clause fixed my issue.

Upvotes: 0

Jack James
Jack James

Reputation: 158

I dont see any issue's with the code as such. Did you make sure that you actually have the values that in the usermeta.meta_value field in the usermeta table that are > 3 ? And second thing would be , is your field meta_value and INT data type ? If yes then you would need '3' here. 

UPDATE usermeta um INNER JOIN cov 
ON cov.user_id = um.user_id 
SET um.meta_value = 3
WHERE um.meta_key = 'quantity' AND
um.meta_value > 3 AND
cov.end_date = '2017-05-18';

Let me know if this works for you.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

There is a difference between '3' and 3. So, I would start with:

UPDATE usermeta um INNER JOIN
       cov 
       ON cov.user_id = um.user_id 
    SET um.meta_value = '3'
WHERE um.meta_key = 'quantity' AND
      um.meta_value + 0 > 3 AND
      cov.end_date = '2017-05-18';

Upvotes: 1

Related Questions