That update query doesn't work?

Question

Hello boys and girls :)

I have that query:

SELECT * FROM customer
INNER JOIN address ON customer.customer_id = address.customer_id
WHERE address.country_id = 176

So i want to update custome.status to 0 I tried with that query but "0 rows affected":

UPDATE customer
INNER JOIN address ON customer.customer_id = address.customer_id
SET customer.status = 0
WHERE address.country_id = 176

I have about 200 ppl with address.country_id = 176 !

Any ideas, thank u !

A sample data and table definitions would be good to reproduce such problems — M Khalid Junaid, Nov 24 '17 at 13:45
Do you have a corresponding record in the `address` table too? — Pred, Nov 24 '17 at 13:48
You sure the data is different then customer.status = 0... if not the query will answer with '0 rows affected' — Raymond Nijland, Nov 24 '17 at 13:51
Try updating the select query to add `customer.status != 0` so that you know how many records are there that needs to be updated. If it's 0 then update query's output is correct. — Ronit Mukherjee, Nov 24 '17 at 13:53
Guys, i''m so sorry to waste your time, the query is right, but status is already 0 ... i really sorry ! — MorganFreeFarm, Nov 24 '17 at 13:55

score 1 · Answer 1 · answered Nov 24 '17 at 13:52

You can try a different approach with EXISTS instead of JOIN

UPDATE
  customer
SET
  status = 0
WHERE
  EXISTS (
    SELECT *
    FROM address A
    WHERE customer.customer_id = A.customer_id AND country_id = 176
  )

Can't recall, but is it possible, that MySQL won't report change for those records where status is already equals to 0?

That update query doesn't work?

1 Answers1